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 ?
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).
WHEN condition. For a large table (~4M records), my query worked much faster if I added a WHERE id in (...) clause.Create a table with the mapping of SKU to new size; update the master table from that.
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:
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.
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.
Make use of OpenXML to resolve your issue
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
Just do...
UPDATE [yourTable] SET Size = 20 WHERE SKU = 'A'
And do this for all values you want to change...
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
UPDATE <table> SET Size = <value> WHERE SKU = '<key>'
3000 times.
If you are that intern, I'd suggest giving us a little more information...
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);
