SQLTeam.com | Weblogs | Forums

Updating a table with a value from another table that has no link


Is it possible to updating a table with a value from another table that has no link.

I have a table with a column accountperiod that holds a date. i want to be able to take the date from that account period and update another table with an account period column with the date where the dates are blank in it.

table a

accountperiod fundcode master id
20/04/2020 LU1612 IE00BDD86369
20/04/2020 LU1301 LU0575255335

table b

accountperiod fundcode master id
LU1551 USN15516AA01
LU1553 US298785GV24

so i want to take the 20/04/2020 and update tables b 2 records with the date.

Yes ..... its possible !!!


any idea on how to go about it

using row number

or if you don't care about order

at random

What if table a had 2 different dates? You can't just run an update and expect it to work properly.

the table will never have different dates in that column. only ever 1 date which is why i was wondering can it be done. if it was different date i wouldn't even be trying

Please provide sample data as ddl and dml

this is how you would do it, but updates the entire table and is dangerous

update b
set date field = (select distinct dt from a)