update statement in oracle with join [duplicate]

Question

I have made this sql and I need to update table with given result

SELECT DISTINCT t1.t1_val3, t1.t1_val4 DECODE (b_val, 'A', 'Its A', 'B', 'Its B', 'C', 'Its C', NULL ) decode_val, FROM t1, t2, t3 WHERE t1.t1_val = t2.t2_val AND t2.t2_val = t3.t3_val AND t3.t3_val2 <> 'PSA' AND t3.t3_val2 = 'Y'

Now using this query I need to update t1 table.

something like this,

update t1 set t1.val5=decode_val where t1.t1_val3= value returned from above query(first column t1_val3) and t1.t1_val4= value returned from above query(2nd column t1_val4)

DB - 10g

Lalit Kumar B · Accepted Answer · 2014-11-04 07:51:12Z

Since you are on 10g, the MATCHED and NOT MATCHED clauses are now optional.

MERGE INTO t1 a USING (SELECT DISTINCT t1.t1_val3, t1.t1_val4 DECODE (b_val, 'A', 'Its A', 'B', 'Its B', 'C', 'Its C', NULL ) decode_val, FROM t1, t2, t3 WHERE t1.t1_val = t2.t2_val AND t2.t2_val = t3.t3_val AND t3.t3_val2 <> 'PSA' AND t3.t3_val2 = 'Y') b ON(a.t1_val3 = b.t1_val_3 and a.t1_val4 = b.t1_val4) WHEN MATCHED THEN UPDATE SET a.t1.val5 = b.decode_val

Lalit Kumar B · Accepted Answer · 2014-11-04 08:05:00Z

1

Try this :

update t1 set t1.val5 =DECODE (b_val, 'A', 'Its A', 'B', 'Its B', 'C', 'Its C', NULL ) FROM t1, t2, t3 WHERE t1.t1_val = t2.t2_val AND t2.t2_val = t3.t3_val AND t3.t3_val2 <> 'PSA' AND t3.t3_val2 = 'Y'