Oracle - update data in first table with rows from second table

user_12196147 0
Viewed 37

How to update 'date_from' (t1) using 'modfied' (t2) when it is like 20/07/20.

So in this case in t1 id's 1 and 2 are to be updated and id 3 stays.

Table 1:

id    date_from
-----------------------
1     13/07/30
2     13/07/30
3     13/07/30

Table 2:

id    name    modified
-----------------------
1     x       20/07/20
2     y       20/07/20
3     z       19/05/10
subquery sql-update oracle sql
3 Answers

Something like this:

update t1 a set
  a.date_from = (select b.modified
                 from t2 b
                 where b.id = a.id
                   and b.modified = date '2020-07-20'
                )
where exists (select null
              from t2 c
              where c.id = a.id
                and c.modified = date '2020-07-20'
             )
user_90979060

If speed matters then,

merge into t1 trg
using 
(
    select  id, modified
    from    t2
    where   modified = date'2020-07-20'
) src
on ( trg.id = src.id )
when matched then update
set trg.date_from = src.modified
where lnnvl(trg.date_from = src.modified);
user_121033810

You know in advance which value needs to be assigned, so you just need to filter which rows should be updated. exists seems sufficient:

update t1 
set date_from = date '2020-07-20'
where exists (
    select 1 from t2 where t2.id = t1.id and t2.modified = date '2020-07-20'
)
user_106767160
Related