CODEWITHSUNDEEP
sqloracle-database
Bigliettiperfavore
Bigliettiperfavore

Reputation: 15

Update from select on multiple tables

I am a bit rusty with Oracle statements - Im trying to create an update statement that makes the a.col2 'Y'. The col3 and col4 contains the same data but column names are different (a lot of columns in both tables). Here is my select statement that identifies all entries:

select a.col1, b.col1, a.col2, b.col2, a.col3, b.col4 from 
table1 a, table2 b
 where a.col1 = 'XX'
 and a.col1 = b.col1
 and b.col5 = 'XX'
 and a.col3 = b.col4
 and a.col2 = b.col2
 and a.col2 = 'X'
 and a.col3 like 'XX%'

This will not work, right (?):

 update table1 a, table2 b
 set a.col1 = 'Y'
 where a.col1 = 'XX'
 and a.col1 = b.col1
 and b.col5 = 'XX'
 and a.col3 = b.col4
 and a.col2 = b.col2
 and a.col2 = 'X'
 and a.col3 like 'XX%'

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

I am guessing that you want exists:

update table1 a
     set a.col1 = 'Y'
     where a.col1 = 'XX' and
           a.col2 = 'X' and
           a.col3 like 'XX%' and
           exists (select 1
                   from table2 b
                   where a.col1 = b.col1 and
                         b.col5 = 'XX' and
                         a.col3 = b.col4 and
                         a.col2 = b.col2
                  );

Upvotes: 1

Related Questions