40Alpha 40Alpha - 9 days ago 4
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.

IE:

UPDATE
table
SET
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

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
          ,column2
          ,column3 AS NewCol2
          ,column4
          ,column5 AS NewCol4
          ,column5
          ,SUBSTRING(column6,1,1) AS NewCol5
          ,column6
          ,RIGHT(column6, LEN(column6) - 1) AS NewCol6
    FROM YourTbl
)
UPDATE UpdateableCTE SET column1=NewCol1
                        ,column2=NewCol2
                        ,column4=NewCol4
                        ,column5=NewCol5
                        ,column6=NewCol6;