CODEWITHSUNDEEP
mysql
Michael Farah
Michael Farah

Reputation: 421

Update date field from MAX date in another table

I'm trying to update a DATETIME field in one table based on the latest date from another. The common field between the two tables is named msisdn. I was trying this

update table1 t1
join table2 t2
on t1.msisdn = t2.msisdn
set t1.bill_attempt = (Select max(event_time) from table2
where t1.msisdn = t2.msisdn)

In table1 I'm getting the latest same event time for all msisdn records. Please assist

Upvotes: 4

Views: 6640

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115620

Either:

UPDATE table1 t1
SET bill_attempt = 
      ( SELECT MAX(event_time) 
        FROM table2 t2
        WHERE t1.msisdn = t2.msisdn
      )

or:

UPDATE
      table1 t1
  JOIN 
      ( SELECT msisdn
             , MAX(event_time) AS event_time
        FROM table2 
        GROUP BY msisdn
      ) AS t2
  ON t1.msisdn = t2.msisdn
SET t1.bill_attempt = t2.event_time

Upvotes: 5

rabudde
rabudde

Reputation: 7722

UPDATE table1 t1
JOIN (SELECT MAX(event_time) AS event_time,msisdn FROM table2 GROUP BY msisdn) t2
ON t1.msisdn = t2.msisdn
SET t1.bill_attempt = t2.event_time

Upvotes: 0

Related Questions