如何从T

如何从T-SQL中的下一个空白列组开始更新一系列列？(How to update a series of columns starting at the next blank column group in T-SQL?)

我试图找出在单个表中更新一组列的最佳方法，从下一个空白组开始。

这种情况的简化表结构是：

+--------+-----------------+----------+-----------+----------+-----------+----------+-----------+ | itemID | itemDescription | prodID01 | prodQty01 | prodID02 | prodQty02 | prodID03 | prodQty03 | +--------+-----------------+----------+-----------+----------+-----------+----------+-----------+ | 1 | Item 1 | ABC123 | 1 | DEF456 | 2 | | | | 2 | Item 2 | GHI789 | 3 | | | | | | 3 | Item 3 | DEF456 | 2 | | | | | +--------+-----------------+----------+-----------+----------+-----------+----------+-----------+

然后我需要做的是在另一个表中查找特定的prodID ，如果找到它，则添加新值然后结束列组以填充数据。

所以第二个数据表是这样的：

+--------+--------+--------+ | prodID | newID | newQty | +--------+--------+--------+ | DEF456 | XYZ012 | 3 | +--------+--------+--------+

在这种情况下，如果找到一个DEF456的prodID，那么这将填充可用的最后一个空白列中的主表。

因此，在示例数据中，项目1将具有在集合03中填充的newID和newQty值，而项目3将具有在集合02中填充的值。

我希望这是有道理的，在我正在使用的实际数据表中有超过20个列组，所以我希望找到一种方法来查找最后一个空列组并在需要时填充数据。

如果它有帮助，如果有该列组的数据，将始终填充prodID XX值作为快速说明。

谢谢。

I am trying to work out the best way of updating a group of columns in a single table, starting at the next blank group.

A simplified table structure for this case would be:

What I then need to do is look for a specific prodID in another table and if it is found then add in new values to then end group of columns to populate the data.

So the second data table would be something like:

+--------+--------+--------+ | prodID | newID | newQty | +--------+--------+--------+ | DEF456 | XYZ012 | 3 | +--------+--------+--------+

In this case where if finds a prodID of DEF456 this would then populate the main table in the last blank column available.

So in the example data Item 1 would have the newID and newQty values populated in set 03 and Item 3 would have the values populated in set 02.

I hope this makes sense, in the actual data table I am working with there are over 20 column groups so I am looking to see a way to find the last empty column group and populate data on the end when needed.

As a quick note on this if it helps the prodIDXX value will always be populated if there is data for that column group.

Thanks.

最满意答案

UPDATE TABLE [Table1] A

第一列将是最简单的：

SET prodID01 = (CASE WHEN prodID01 IS NOT NULL THEN prodID01 ELSE (SELECT newID FROM [Table2] WHERE [Table2].prodID = [Table1].prodID01)) SET prodQty1 = (CASE WHEN prodID01 IS NOT NULL THEN prodQty1 ELSE (SELECT newQty FROM [Table2] WHERE [Table2].prodID = [Table1].prodID01))

从那里你将有很多嵌套语句：

SET prodID02 = (CASE WHEN prodID02 IS NOT NULL THEN prodID02 ELSE (CASE WHEN prodID01 IS NOT NULL THEN (SELECT newID FROM [Table2] WHERE [Table2].prodID = [Table1].prodID02)) SET prodQty02 = (CASE WHEN prodID02 IS NOT NULL THEN prodQty02 ELSE (CASE WHEN prodID01 IS NOT NULL THEN (SELECT newQty FROM [Table2] WHERE [Table2].prodID = [Table1].prodID02)) SET prodID03 = (CASE WHEN prodID03 IS NOT NULL THEN prodID03 ELSE (CASE WHEN prodID01 IS NOT NULL THEN (CASE WHEN prodID02 IS NOT NULL THEN (SELECT newID FROM [Table2] WHERE [Table2].prodID = [Table1].prodID03))) SET prodQty03 = (CASE WHEN prodID03 IS NOT NULL THEN prodQty03 ELSE (CASE WHEN prodID01 IS NOT NULL THEN (CASE WHEN prodID02 IS NOT NULL THEN (SELECT newQty FROM [Table2] WHERE [Table2].prodID = [Table1].prodID03)))

等等

如果你有超过20列，就像你说的那样，这将是一个很长的查询，所以我同意@Shnugo这可能是一个设计问题。

UPDATE TABLE [Table1] A

The first column will be the simplest:

from there you will have a lot of nested statements:

etc.

If you have over 20 columns, like you stated, this is going to be a long query, so I would agree with @Shnugo that this might be a design issue.

更多推荐