CODEWITHSUNDEEP
sqlsql-serverjoinsql-updatemax
user7909802
user7909802

Reputation:

Update latest data

I would like to UPDATE in the FIRST_DATE field in the MASTER_DB table. However, I would like to update with the date of the most recent record in the RECORD_DB table.

UPDATE
  MASTER_DB
SET
  MASTER_DB.FIRST_DATE = RECORD_DB.DATE_R
FROM
  MASTER_DB 
INNER JOIN
  RECORD_DB
ON
  MASTER_DB.ID = RECORD_DB.ID

Example:

I)Table MASTER_DB

ID   FIRST_DATE
122

II)Table RECORD_DB

ID   DATE_R
122  2015-01-01
122  2016-01-02
122  2017-01-03

The update of the FIRST_DATE field of the MASTER_DB table must receive the highest date for the same ID as the RECORD_DB table, that is, 2017-01-03

Tthe output in the MASTER_DB table should be:

 ID   FIRST_DATE
 122  2017-01-03

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

The proper syntax in MySQL would be:

UPDATE MASTER_DB m JOIN
       (SELECT ID, MAX(DATE_R) AS MIN_DATE_R
        FROM  RECORD_DB
        GROUP BY ID
       ) R
       ON R.ID = m.ID
    SET M.FIRST_DATE = R.MAX_DATE_R;

The syntax in SQL Server:

UPDATE m
    SET M.FIRST_DATE = R.MAX_DATE_R
FROM MASTER_DB m JOIN
     (SELECT ID, MAX(DATE_R) AS MAX_DATE_R
      FROM  RECORD_DB
      GROUP BY ID
     ) R
     ON R.ID = m.ID;

Upvotes: 1

GMB
GMB

Reputation: 222472

One option is a correlated subquery:

update master_db m
set m.first_date = (select max(r.date_r) from record_db r where r.id = m.id)

If there are master rows without any record row, and you don't want to update those to null, then a join is more appropriate - or an updatable CTE:

with m
set m.first_date = r.max_date_r
from master_db m
inner join (select id, max(r.date_r) max_date_r record_db goup by id) r 
    on r.id = m.id

Upvotes: 0

Related Questions