CODEWITHSUNDEEP
sqlselectdb2sql-updateibm-midrange
user1420914
user1420914

Reputation: 279

Update same field in SQL based on different criteria in AS400 SQL

A question recently came up on whether it's possible to perform an update on the same field in the file based on 2 different criteria. We introduced a step to run 2 different SQL's at the end of day processing, so the initial problem has been relieved. However, for my own knowledge on the subject, I'm curious if it is possible to do one SQL statement similar to the below?

UPDATE MYFILE 
SET FIELD1 = 'ABC' where FIELD2 = '123' and FIELD3 = '456',
SET FIELD1 = 'XYZ' where FIELD2 = '789' and FIELD3 = '123'

Is something like that possible within one statement, or would you have to break it out in 2 statements like we ended up doing?

Thanks in advance!

Upvotes: 1

Views: 102

Answers (1)

GMB
GMB

Reputation: 222472

You can use a case expression within the update statement:

update myfile
set field1 = case
    when field2 = '123' and field3 = '456' then 'abc'
    when field2 = '789' and field3 = '123' then 'xyz'
end
where 
    (field2 = '123' and field3 = '456')
    or (field2 = '789' and field3 = '123')

Upvotes: 4

Related Questions