update statement in oracle with join

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

score 2 · Accepted Answer · answered Nov 04 '14 at 07:51

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

score 1 · Answer 2 · edited Nov 04 '14 at 08:05

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'

edited Nov 04 '14 at 08:05

Lalit Kumar B

47,486
13
97
124

answered Nov 04 '14 at 07:56

King_Fisher

1,171
8
21
51

Please verify your post before submitting, always use code tags and check if the tags are properly applied. – Lalit Kumar B Nov 04 '14 at 08:05
im new here.sorry :) – King_Fisher Nov 04 '14 at 08:07
No problem. I have edited your answer. – Lalit Kumar B Nov 04 '14 at 08:36
mark it as Answer ,this may help someone – King_Fisher Nov 04 '14 at 09:41
I have voted up for your answer and marked lalit's solution as answer, he provided solution earlier. Thanks :) – Pravin Satav Nov 04 '14 at 09:51

update statement in oracle with join

2 Answers2