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
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'
)
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);
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'
)
