40Alpha 40Alpha - 1 year ago 80
SQL Question

Setting table columns to sibling columns

Due to some dirty data I have an issue where I need to set some columns in a table equal to their sibling columns after some string modification.


column1 = column2
,column2 = column3
,column4 = column5
,column5 = SUBSTRING(column6,1,1)
,column6 = RIGHT(column6, LEN(column6) - 1)

However this is not going to work (because these set commands are not executing in order). Is there a better way to go about this than individually writing out a subquery for each?

Answer Source

You might try it wiht an updateable CTE

wanted to see if there was an cool way - uhm - cool enough?

WITH UpdateableCTE AS
    SELECT column1 
          ,column2 AS NewCol1
          ,column3 AS NewCol2
          ,column5 AS NewCol4
          ,SUBSTRING(column6,1,1) AS NewCol5
          ,RIGHT(column6, LEN(column6) - 1) AS NewCol6
    FROM YourTbl
UPDATE UpdateableCTE SET column1=NewCol1
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download