SQL (oracle) Update some records in table using values in another table
<p>I have to tables :</p>

<pre><code>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
</code></pre>

<p>I want to update :</p>

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

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

<p>Here my code :</p>

<pre><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&lt;&gt;'0'; --(for doing the update only if VAL1 not already equal to 0)
</code></pre>

<p>The expected result is :</p>

<pre><code>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
</code></pre>

<p>The result I get is :</p>

<pre><code>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
</code></pre>

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