CODEWITHSUNDEEP
mysqlsqlsql-update
Aram Papazian
Aram Papazian

Reputation: 2539

Update table on itself MySQL

I can't figure out how to make this query run.... I want to update the table so that dependent on the uuid it grabs the lowest of the 'last_updated' and updates the 'created_on' ... I keep getting 'cant specify target table' although I don't know why =/ Is it a recursion issue?

UPDATE dlp.address AS t1
SET created_on = (SELECT MIN(last_updated) 
                  FROM dlp.address AS t2 
                  WHERE t1.addressuuid = t2.addressuuid);

Upvotes: 4

Views: 4206

Answers (2)

babaoqi
babaoqi

Reputation: 96

You can use :

UPDATE dlp.address AS t1

  , (SELECT addressuuid, MIN(last_updated) AS minDate 
       FROM dlp.address GROUP BY addressuuid) AS t2

SET t1.created_on = t2.minDate 

WHERE t1.addressuuid = t2.addressuuid

Upvotes: 1

John Woo
John Woo

Reputation: 263843

UPDATE  dlp.address AS t1
        INNER JOIN 
        (
            SELECT  addressuuid, MIN(last_updated) minDate
            FROM    dlp.address
            GROUP BY addressuuid
        ) AS t2 
            ON t1.addressuuid = t2.addressuuid
SET     t1.created_on = t2.minDate

Upvotes: 3

Related Questions