SQL Updating Rows Without Knowing Column Name

问题:

I am attempting to create a stored procedure that will update a specific row and column location based on user input. My table has approximately 100 columns utilizing names in progressive increments. Ex Page1, Page2, Page3, Page 4.... etc. The data must also be updated in progressive increments as users finish different versions of each page.

When the procedure is called I need it to find the user's row(Page1 is the key and unique), and put the information in where their version of the file is saved into the first NULL column. I have seen talk about using cursors for similar applications but I am not sure if this is the proper solution.

 ----------------------------------------------------
 |  Page1    |   Page2    |   Page3    |   Page4    |   
 ----------------------------------------------------
 |  /pg1.htm |   /pg2.htm |  /pg3.htm  |   NULL     |
 ----------------------------------------------------
 | /pg1.doc  |   /pg2.doc |   NULL     |   NULL     |
 ----------------------------------------------------
 | /pg1.pdf  |  NULL      |   NULL     |   NULL     |
 ----------------------------------------------------

I need the procedure to update the row sequentially each time with one piece of data when it is called. My issue is in making this scaleable instead of limiting it with 100 + IF statements.

The pseudo code I cooked up looks like this but is terribly inefficient:

 FIND ROW that matches unique key
 LOOP Find_NULL
     IF Column2 == NULL
         UPDATE DATA HERE
     ELSE IF Column3 == NULL
         UPDATE DATA HERE
     ELSE IF Column4 == NULL
         UPDATE DATA HERE
     ELSE IF.... and on and on
 END LOOP Find_NULL

I am utilizing MySQL which I have been told does not support Dynamic SQL. I attempted to create a variable and modify it as I went through the data to store the next NULL column however this did not work.

If anyone has any solutions or advice I would appreciate it.

Thanks in advance.

回答1:

At first glance, you seem to be suffering from rather poor database design.

You don't want to name columns "Page1", "Page2" ... "Page 100", and then have these columns be NULL much of the time. This violates sound database design. You might want to review concepts such as database normalization (e.g., first normal form, second and so on).

I think you would be much better off having a column named "Page" and then each row would have a value of 1 through 100 along with the information related to the page. This way you wouldn't need to try to dynamically piece together columns names when forming an insert/update query.

回答2:

I would recommend that you convert the columns into rows. That way you just need to insert a new row, which is much more efficient.

The table would contain a sequence column to preserve the order of the values.

User,Seq,Val
1,1,/pg1.htm
1,2,/pg2.htm
1,3,/pg3.htm
2,1,/pg1.doc
2,2,/pg2.doc
3,1,/pg1.pdf

回答3:

Your table is not in first normal form, and therefore you're suffering from all of the problems germane to that design problem.

If you look at that link, you'll see that the suggested solution is to create a separate table linking the repeating groups to the parent entity (your current row). In your specific case, this means you'll have a separate Pages table linking page numbers (1, 2, 3, ..., n) to the unique key in this (parent) table, and store the data pertinent to that page in the Pages table.