CODEWITHSUNDEEP
sqloracle-database
user1969650
user1969650

Reputation: 45

updating a table upon two conditions from the same table

I have a table with columns like book, startyear, endyear and author.

I need to update startyear and endyear columns simultaneously depending on the following conditions,

STARTYEAR = ENDYEAR where STARTYEAR=0 and ENDYEAR != 0 
also
ENDYEAR = STARTYEAR  where ENDYEAR =0 and STARTYEAR != 0

It is possible with 

UPDATE Table SET STARTYEAR = ENDYEAR WHERE STARTYEAR=0 AND ENDYEAR<>0; 
UPDATE Table SET ENDYEAR = STARTYEAR  WHERE ENDYEAR =0 AND STARTYEAR<>0;

How can I write these 2 queries in a single query?

Upvotes: 1

Views: 10381

Answers (5)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

Another way would be:

UPDATE Table 
SET STARTYEAR = STARTYEAR + ENDYEAR
  , ENDYEAR   = STARTYEAR + ENDYEAR
WHERE STARTYEAR = 0 AND ENDYEAR <> 0
   OR ENDYEAR = 0 AND STARTYEAR <> 0 ;

But I really don't see any reasons for doing this. You can use the 2-statements way you have, inside a transaction. It may even be more efficient than any convoluted single statement.

Upvotes: 3

Techmonk
Techmonk

Reputation: 1469

You can use or and update bot fields unless you have triggers stopping you from doing this for cases where startyear = endyear = 0;

UPDATE Table SET STARTYEAR = STARTYEAR+ ENDYEAR, ENDYEAR = STARTYEAR+ENDYEAR WHERE STARTYEAR=0 OR ENDYEAR=0

Upvotes: 1

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60493

update table
set 
startYear = case when startyear = 0 and endyear<>0 then endyear else startYear end,
endYear = case when endyear = 0 and startyear <> 0 then startyear else endYear end
where startyear = 0 or endyear = 0;

Upvotes: 4

Orangecrush
Orangecrush

Reputation: 1990

This should work,

update test
   set startyear = (case when startyear = 0 AND endyear <> 0 then endyear else startyear end),
       endyear   = (case when endyear = 0 AND startyear <> 0 then startyear else endyear end);

Upvotes: 0

avi
avi

Reputation: 912

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATE statements are treated like one. You can also batch them to avoid a round trip.

BEGIN TRANSACTION

UPDATE Table SET STARTYEAR = ENDYEAR WHERE STARTYEAR=0 AND ENDYEAR<>0; 

UPDATE Table SET ENDYEAR = STARTYEAR  WHERE ENDYEAR =0 AND STARTYEAR<>0;

COMMIT

but in your case you can also use something like the answer in this post.

Upvotes: 0

Related Questions