SQL在不知道列名称的情况下更新行 [英] SQL Updating Rows Without Knowing Column Name

问题描述

我正在尝试创建一个存储过程，将根据用户输入更新特定的行和列位置。我的表有大约100列使用逐渐增量的名称。例如第1页，第2页，第3页，第4页....等等。当用户完成每个页面的不同版本时，数据也必须以递增的增量更新。

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.

过程被调用我需要它来找到用户的行（Page1是密钥和唯一），并将信息放在其中保存文件的版本的第一个NULL列中。我已经看到谈论使用光标的类似应用程序，但我不知道这是否是正确的解决方案。

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     |
 ----------------------------------------------------

我需要程序每次更新一行数据被调用时。我的问题是使这个可缩放的，而不是用100 + IF语句限制。

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

我使用MySQL，我已经被告知不支持动态SQL。我试图创建一个变量并修改它，当我通过数据存储下一个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.

提前感谢。

推荐答案

一看，你似乎正在遭受相当差的数据库设计。

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

您不想命名列Page1，Page2...第100页，然后大多数时间将这些列设置为NULL。这违反了声音数据库设计。你可能想要审查数据库规范化等概念（例如，第一个正常形式，第二个等等）。

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).

我认为你会更好的页面，然后每行将具有值1到100以及与页面相关的信息。这样，在形成插入/更新查询时，您不需要尝试动态地将列名称组合在一起。

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.

这篇关于SQL在不知道列名称的情况下更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！