CODEWITHSUNDEEP
sqloracle-databasesql-update
Venkat Chidambaram
Venkat Chidambaram

Reputation: 73

How to write an UPDATE statement when we need to check a condition?

I want to write an update statement in Oracle SQL to set True to a few properties only when another property in the same table is true.

    name   |    value
----------------------
property1       False
property2       False
property3       True

UPDATE table_xyz 
   SET value = 'True' 
WHERE name in ('property1', 'property2')

only when property3 = 'True';

Upvotes: 1

Views: 71

Answers (2)

PSK
PSK

Reputation: 17943

You can try like following using EXISTS.

UPDATE table_xyz 
   SET value = 'True' 
WHERE name in ('property1', 'property2') 
AND EXISTS
(
 SELECT 1 FROM table_xyz WHERE name = 'property3' AND value = 'True'
)

Upvotes: 2

Littlefoot
Littlefoot

Reputation: 142713

As you said - add that condition:

UPDATE table_xyz a
   SET a.value = 'True' 
WHERE a.name in ('property1', 'property2') 
  AND 'True' = (SELECT b.value 
                FROM table_xyz b
                WHERE b.name = 'property3'
               )

(presuming that there's only one row whose name is property3; otherwise, you'll get TOO_MANY_ROWS error).

Upvotes: 2

Related Questions