SQL (oracle) Update some records in table using values in another table

Question

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
1          N          01/08/2015
2          null       null
3          O          05/04/2016
3          O          02/02/2015
4          O          01/07/2015
5          O          03/02/2015
5          N          10/01/2014
5          O          12/04/2015

I want to update :

• column VAL1 (of Table1) with '0', if VAL2 (of Table2) is equal to 'O'
• column DATE1 (of Table1) with the earliest DATE2 (of Table2) for each ID (here my problem)

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

Here my code :

UPDATE Table1 t1
SET t1.VAL1 = '0',
t1.DATE1 = (select min(t2.DATE2) --To take the first DATE for each ID where VAL2='O' (not working fine)
            FROM Table2 t2, Table1 t1
            WHERE trim(t2.ID) = trim(t1.ID)
            AND VAL2='O')
WHERE EXISTS (SELECT NULL
              FROM Table2 t2
              WHERE trim(t2.ID) = trim(t1.ID)
              AND t2.Table2 = 'O')
AND VAL1<>'0'; --(for doing the update only if VAL1 not already equal to 0)

The expected result is :

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

The result I get is :

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

My problem is that the DATE1 is always updated with the same date, regardless of the ID.

Answer 1

You shouldn't have a second reference to table1 in the first subquery; that is losing the correlation between the subquery and the outer query. If you run the subquery on its own it will always find the lowest date in table2 for any ID that has val2='O' in table1, which is 10/01/2014. (Except your sample data isn't consistent; that's actually N so won't be considered - your current and expected results don't match the data you showed, but you said it isn't real). Every row eligible to be updated runs that same subquery and gets that same value.

You need to maintain the correlation between the outer query and the subquery, so the subquery should use the outer table1 for its join, just like the second subquery already does:

UPDATE Table1 t1
SET t1.VAL1 = '0',
t1.DATE1 = (select min(t2.DATE2)
            FROM Table2 t2
            WHERE trim(t2.ID) = trim(t1.ID)
            AND VAL2='O')
WHERE EXISTS (SELECT NULL
              FROM Table2 t2
              WHERE trim(t2.ID) = trim(t1.ID)
              AND t2.Val2 = 'O')
AND VAL1<>'0';

Answer 2

MYSQL Solution

Hope this MySql syntax also works with ORACLE.

The issue with the SQL is that it only consider the records with VAL2=='O' when calculating the earliest date. So the last record have the date as shown in table below. Record "5 N 10/01/2014" is not considered.

UPDATE Table1, (SELECT * FROM (SELECT * FROM table2 WHERE VAL2='O' ORDER BY ID, DATE1) X GROUP BY X.ID) T2
SET Table1.DATE1=T2.DATE1, Table1.VAL1=0
WHERE Table1.ID=T2.ID

..

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

Tested on MySql 5.6.14

Answer 3

You can use this UPDATE statement.

UPDATE TABLE1 T1
SET T1.VAL1 = '0',
T1.DATE1 = (SELECT MIN(T2.DATE2) 
                  FROM TABLE2 T2
                  WHERE TRIM(T2.ID) = TRIM(T1.ID)
                  AND T2.VAL2='O')
WHERE T1.ID IN (SELECT T2.ID FROM TABLE2 T2 WHERE T2.VAL2='O')

Hope it will help you.