CODEWITHSUNDEEP
sqloracle-sqldeveloper
baboufight
baboufight

Reputation: 133

SQL (oracle) Update some records in table A using values in table B

I have to tables :

Table1
--------------------------------
ID         VAL1       DATE1
--------------------------------
1          1          20/03/2015
2          null       null
3          1          10/01/2015
4          0          12/02/2015
5          null       null

Table2
--------------------------------
ID         VAL2       DATE1
--------------------------------
1          N          02/06/2015
2          null       null
3          O          05/04/2015
4          O          01/07/2015
5          O          03/02/2015

I want to update :

(This two tables are not so simple, it's just for illustration, they can be joined with the ID column).

So the expected result is :

Table1
--------------------------------
ID         VAL1       DATE1
--------------------------------
1          1          20/03/2015
2          null       null
3          0          05/04/2015
4          0          01/07/2015
5          0          03/02/2015

Here my code :

UPDATE Table1 t1
SET t1.VAL1 = '0',
SET t1.DATE1 = (SELECT t2.DATE2 from Table2 t2
                INNER JOIN Table1 t1
                ON trim(t2.ID)=trim(t1.ID))
WHERE EXISTS (SELECT NULL
              FROM Table2 t2
              WHERE trim(t2.ID) = trim(t1.ID)
              AND t2.Table2 = 'O');

The part that make my code not working is : (esle working)

SET t1.DATE1 = (SELECT t2.DATE2 from Table2 t2
                    INNER JOIN Table1 t1
                    ON trim(t2.ID)=trim(t1.ID))

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You want a correlated subquery, not a join in the subquery. So, assuming the rest of the logic is correct:

UPDATE Table1 t1
    SET t1.VAL1 = '0',
        t1.DATE1 = (SELECT t2.DATE2 
                    FROM Table2 t2
                    WHERE trim(t2.ID) = trim(t1.ID))
    WHERE EXISTS (SELECT 1
                  FROM Table2 t2
                  WHERE trim(t2.ID) = trim(t1.ID) AND
                        t2.Table2 = 'O'
                 );

Upvotes: 2

Related Questions