SQLTeam.com | Weblogs | Forums

Changing a SELECT into an INSERT

sql2008

#1

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!

Below is the code:

SELECT distinct [ExpressDB].[dbo].[epic_LineItems].[LineItemId]

  ,[VersionStamp]
  ,[OrderId]
  ,[Name]
  ,[ManufacturerName]
  ,[Quantity]
  ,[UnitPrice]
  ,[Notes]
  ,[ItemType]
  ,[StatusId]
  ,[ContractId]
  ,[Description]
  ,[PartNumber]
  ,[SkuId]
  ,Case

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

FROM [ExpressDB].[dbo].[epic_LineItems]


#2

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')
;

#3

@bitsmed:

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


#4

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


#5

@wave2453: So do you have the full UPDATE now, or do you still need coding tips?


#6

Sorry missed that one - well spotted


#7

@bitsmed @ScottPletcher

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


#8

Get rid of it, you don't need it:

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

#9

Great! Thanks for your help @ScottPletcher and @bitsmed I appreciate it! It's good to know there is a helpful community out here to teach the newbies :slight_smile:


#10

This can be simplified to:

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

#11

@jeffw8713
Thanks for the tip :+1:

I appreciate the info on this.


#12

I guess, compared to "actually going to the Disk to get data" the CPU difference is trivial, but I've often wondered how

CASE StatusID 
    WHEN 1 THEN
    WHEN 2 THEN
...

performs compared to

CASE  
    WHEN StatusID IN (1, 2) THEN
    WHEN StatusID IN (3, 4) THEN
...