在SQL中更新具有不同值的多个行

I have a table like this:

我有这样一张桌子:

SKU            Size
A              10
B              10
C              10
D              10
E              10
F              10
G              10

I want to change it to:

我想将其更改为:

SKU            Size
A              20
B              10
C              30
D              10
E              80
F              10
G              60

I have more than 3000 rows of records to update. How can I do that with SQL update command ?

我有超过3000行记录要更新。如何使用SQL更新命令执行此操作?

6 个解决方案

#1

UPDATE T
SET Size = CASE SKU
    WHEN 'A' THEN 20
    WHEN 'B' THEN 10
    WHEN 'C' THEN 30
    WHEN ...
END

Or there may be a formula for calculating the size, but you've failed to give it in your question (Or we may have to switch to a more complex CASE expression, but again, too little detail in the question).

或者可能有一个计算大小的公式,但你没有在你的问题中给出它(或者我们可能不得不切换到更复杂的CASE表达式,但同样,问题中的细节太少)。

#2

Create a table with the mapping of SKU to new size; update the master table from that.

创建一个表格,其中包含SKU到新大小的映射;从那里更新主表。

Many dialects of SQL have a notation for doing updates via joined tables. Some do not. This will work where there is no such notation:

许多SQL方言都有通过连接表进行更新的表示法。有些人没有。这将适用于没有这种表示法的地方:

CREATE TABLE SKU_Size_Map
(
     SKU     CHAR(16) NOT NULL,
     Size    INTEGER NOT NULL
);
...Populate this table with the SKU values to be set...
...You must have such a list...

UPDATE MasterTable
   SET Size = (SELECT Size FROM SKU_Size_Map
                WHERE MasterTable.SKU = SKU_Size_Map.Size)
 WHERE SKU IN (SELECT SKU FROM SKU_Size_Map);

The main WHERE condition is need to avoid setting the size to null where there is no matching row.

主WHERE条件需要避免在没有匹配行的情况下将大小设置为null。

You can probably also do it with a MERGE statement. But the key insight for any of these notations is that you need a table to do the mapping between SKU and size. You either need a table or you need an algorithm, and the sample data doesn't suggest an algorithm.

您也可以使用MERGE语句执行此操作。但是,对于这些符号中的任何一个的关键见解是,您需要一个表来执行SKU和大小之间的映射。您需要一个表或者您需要一个算法,并且示例数据不建议算法。

#3

Make use of OpenXML to resolve your issue

利用OpenXML解决您的问题

example

declare @i int

exec sp_xml_preparedocument @i output, 
'<mydata>
  <test xmlID="3" xmlData="blah blah blah"/>
  <test xmlID="1" xmlData="blah"/>
</mydata>'

insert into test 
select xmlID, xmlData 
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30))
where xmlID not in (select xmlID from test)

update test
set test.xmlData = ox.xmlData
from OpenXml(@i, 'mydata/test')
with (xmlID int, xmlData nvarchar(30)) ox
where test.xmlID = ox.xmlID

exec sp_xml_removedocument @i

#4

Just do...

UPDATE [yourTable] SET Size = 20 WHERE SKU = 'A'

And do this for all values you want to change...

并为要改变的所有值执行此操作...

#5

Well, if you don't have a formula to calculate your Sizes, and you don't have a file or an Excel sheet with the data that you can massage into your table, you'll just have to get some luckless intern to type something like

好吧,如果你没有一个公式来计算你的尺码,而你没有一个文件或一张Excel表格,你可以在你的桌子上按下这些数据,你只需要一些不费力的实习生就可以输入就像是

 UPDATE <table> SET Size = <value> WHERE SKU = '<key>'

3000 times.

If you are that intern, I'd suggest giving us a little more information...

如果你是那个实习生,我建议给我们更多信息......

#6

-2

Since you wanted to change the whole column, drop that particular column by using this:

由于您想要更改整个列,请使用以下方法删除该特定列:

ALTER TABLE table_name
DROP COLUMN column_name;

then create a new column using:

然后使用以下命令创建新列:

ALTER TABLE table_name
ADD column_name varchar(80);

#1

UPDATE T
SET Size = CASE SKU
    WHEN 'A' THEN 20
    WHEN 'B' THEN 10
    WHEN 'C' THEN 30
    WHEN ...
END