0

I am new to Oracle SQL and need some help with the writing the following query. Any guidance would be greatly appreciated. I am not sure I am going down the correct path with this or not.

I have two tables t1 and t2. t1 contains four fields f1...f4. t2 contains the same four fields. I need to update t1.f1 and t1.f4 with the values from t2.f1 and t2.f4, respectively, where t1.f2 = t2.f2 and t1.f3 = t2.f3. If there is a row that does not match these conditions, The row should not be updated.

Am I on the right path or completely lost?

UPDATE t1 
SET (t1.f1=t2.f1, 
     t1.f4=t2.f4)
FROM t1
INNER JOIN (SELECT  t2.f1, t2.f4 FROM t2) 
ON t1.f2=t2.f2 AND t1.f3=t2.f3
WHERE EXISTS (SELECT t2.f1, t2.f4
              FROM t2
              WHERE t1.f2=t2.f2 AND t1.f3=t2.f3);
wsquared
  • 41
  • 4
  • Please study the following Oracle documentation: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm It contains examples you need. Another option is MERGE statement: http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9016.htm#i2081218 – Dmitry Nikiforov Feb 04 '14 at 20:37

2 Answers2

0

Might be easier if you use a MERGE statement. Something like:

MERGE INTO t1 USING (SELECT f1,f2,f3,f4 FROM t2) t2 ON (t1.f2=t2.f2 AND t1.f3 = t2.f3) WHEN MATCHED THEN update set t1.f1 = t2.f1, t1.f4 = t2.f4;

Ian Flanagan
  • 1
0

If you are using Oracle version 9i or above 'MERGE' is the best way to do it.

Or you can rewrite your above query like this

UPDATE table1 t1 
SET (t1.f1,t1.f2) = (
 select t2.f1,t2.f4 from table2 t2 
            where t1.f2 = t2.f2 and t1.f3 = t2.f3)
WHERE EXISTS (SELECT 1
          FROM table2 t2
          WHERE t1.f2=t2.f2 AND t1.f3=t2.f3);
Raj
  • 1