Hi!
I am new to SQL Server 2008 and am trying to do a mass change of all the data in a column called StatusID. I have written a SELECT statement that shows what I want the data to change to but I don't know how to write the INSERT statement. If someone could offer some advice or how to write it I would appreciate it. Please let me know if you have any questions. Thanks In advance!
When
StatusId IS Null Then '52'
When
StatusID = '51' Then '51'
When
StatusID = '5' Then '51'
When
StatusID = '24' Then '50'
When
StatusID='50' Then '50'
When
StatusID='49' then '49'
When
StatusID='52' then '52'
When
StatusID='53' then '53'
Else 49
End as StatusID
When changing data, you use update (not insert).
No need to update rows, when no change needed (ex. when StatusID='51' then '51').
Before doing the update in production, do it on a copy of the table (just in case the update goes haywire).
Try this:
update ExpressDB.dbo.epic_LineItems
set StatusID=case StatusID
when '5' then '51'
when '24' then '50'
else '52'
end
where StatusID is null
or StatusID in ('5','24')
;
What about the "Else 49"? It's at least possible that there are values such as, for example, '7' or '10' that would be reset by the original code but not by your code.
(@wave2453: btw, that should be Else '49', with quotes around the 49, to be consistent with the other entries.)
@bitsmed
Thanks for the help! I didn't even know about update. However @ScottPletcher is correct about the "Else 49" the numbers run from 1 to 53 with nulls involved. There are some that need to be set to a specific number and all others will be set to 49. However, the nulls need to be set to 52. I used the "'52' then '52'" to keep the else from catching those.
I almost have it I think I just don't know what to do with the where statement.
update ExpressDB.dbo.epic_LineItems
set StatusID=case StatusID
when '5' then '51'
when '24' then '50'
when '49' then '49'
when '50' then '50'
when '51' then '51'
when '52' then '52'
when '53' then '53'
when is null then '52'
else '49'
end
where StatusID is null
or StatusID in ('5','24')
--I do not know what to do with the where statement. since the numbers range from 1 to 53 how would I set that?
;
UPDATE ExpressDB.dbo.epic_LineItems
SET StatusID=
CASE WHEN StatusID IS NULL THEN '52'
ELSE CASE StatusID
WHEN '05' THEN '51' /*if needed, just in case*/
WHEN '5' THEN '51'
WHEN '24' THEN '50'
WHEN '49' THEN '49'
WHEN '50' THEN '50'
WHEN '51' THEN '51'
WHEN '52' THEN '52'
WHEN '53' THEN '53'
ELSE '49'
END
END
UPDATE ExpressDB.dbo.epic_LineItems
SET StatusID=
CASE WHEN StatusID IS NULL THEN '52'
WHEN StatusID IN ('49', '50', '51', '52', '53') THEN StatusID
WHEN StatusID IN ('05', '5') THEN '51'
WHEN StatusID = '24' THEN '50'
ELSE '49'
END