CODEWITHSUNDEEP
sql
ShanksPranks
ShanksPranks

Reputation: 417

Update columns in sequence

I am busy writing a piece of SQL where I only want to update column 2 if the result from column 1 fits certain criteria.

In the example below I am looking for some kind of syntax which will allow me to execute one update before proceeding to the next so I can use the result from the first in the second.

CREATE TABLE #Rows 
(
RowID INT IDENTITY
,Num1 INT 
,Num2 INT 
)

INSERT INTO #Rows
(
Num1,Num2)
VALUES
(1,10),
(1,10)

SELECT
    *
FROM
    #Rows

UPDATE
    #Rows
SET 
    Num1 = CASE WHEN Num1 + Num2 < 20 THEN 10 --Update 1 : I want this statement to execute first 
           END
   ,Num2 = CASE WHEN Num1 + Num2 = 20 THEN 100 ELSE 700 --Update 2 : I want this statement to execute after Update 1  
           END  
SELECT
    *
FROM
    #Rows

DROP TABLE #Rows

The result I get is as follows:

RowID   Num1    Num2
1       10      700
2       10      700

I am hoping to get the following result:

RowID   Num1    Num2
1       10      700
2       10      100

Any ideas?

Upvotes: 0

Views: 171

Answers (2)

Charleh
Charleh

Reputation: 14002

Since Num1 is set to the result of an expression you can just re-use that expression in the second CASE statement:

UPDATE
    #Rows
SET 
    Num1 = CASE 
               WHEN Num1 + Num2 < 20 THEN 10
           END,
   Num2 = CASE 
               WHEN CASE
                        WHEN Num1 + Num2 < 20 THEN 10 
                        ELSE Num1
                    END + Num2 = 20 THEN 100 
                    ELSE 700 --Update 2 : I want this statement to execute after Update 1  
           END

Yeah it's messy but it should work.

I suppose the alternative is to wrap the whole thing in a transaction and do two updates

Upvotes: 1

Federico Ponte
Federico Ponte

Reputation: 91

I think you cant do that, you will have to two update statements or change the CASE conditions. SQL syntax is not designed for that, as far as I know.

Read more info here:

http://dev.mysql.com/doc/refman/5.0/es/update.html

http://www.postgresql.org/docs/9.1/static/sql-update.html

Upvotes: 0

Related Questions