Update in Oracle 11g

Question

I have to update a field in a table. I'm using following queries. Help me, which one is right?

update table1
set col1=<value>,col2=<value>....
from table1 t,table2 s
where t.id=s.num
  and s.code='abc';

or

update table1
set col1=<value>,col2=<value>....
where table1.id=table2.num
  and table2.code='abc';

Which one is right? Or both are incorrect? Please suggest some way...

Solution 1

Neither is correct. It's not clear from your fragment what you are trying to do, but the syntax to update one table with values from another would be more like:

update table1
set (col1, col2) =
( select col1, col2
  from   table2
  where  table2.num = table1.id
  and    table2.code = 'abc'
)
where table1.id in (select num from table2);

The final WHERE clause is to prevent updating all non-matched table1 rows with nulls.

Another method that works when table1 is "key preserved" in the query is:

update
( select table1.id, table1.col1, table1.col2
  ,      table2.col1 as new_col1, table2.col as new_col2
  from   table1
         join table2 on table2.num = table1.id
)
set col1 = new_col1, col2 = new_col2;

Solution 2

MERGE
INTO table1 t1
USING (
 SELECT *
 FROM table2
 WHERE table2.code = 'abc'
) t2
ON (t1.id = t2.num)
WHEN MATCHED THEN
  UPDATE
  SET col1 = t2.value1, col2 = t2.value2

Remember to always qualify the SET statement, ie t2.value1. No error is thrown when ambiguous. In other words, SET col1 = value1 would be t2.value1, but would be t1.value1 if t2.value1 was missing.

Solution 3

Based on your last remark, you need to update table table1 with fixed values that are not in a database table. But only for rows in table1 that match with specific rows in table 2. In that case you can use this statement:

update table1 t1
set t1.col1='value',
t1.col2='value2',
t1.col3='value3'
where exists ( select ''
               from table2 s 
               where  t1.id=s.num 
               and s.code='abc'
);