CODEWITHSUNDEEP
mysqljoinsql-updateinner-join
Tyler_1
Tyler_1

Reputation: 306

MySQL - Update/Set a column in one table equal to MAX value from another table

I've found a number of hits for what I'm trying to do, but only when doing a SELECT and I haven't been able to get those methods to work with an UPDATE.

Table: Project
Site | I-Date | Status

Table: Schedule
Site | F-Date | Status

Here's the query I have now, but I want to guarantee that the project date is set only to the MAX date as there could be multiple entries for each site, but with different dates in schedule.

UPDATE project
    INNER JOIN schedule on project.site = schedule.site 
    SET project.i-date = schedule.f-date, project.status = 'complete'
    WHERE project.site = 'site123'

I tried doing this:

UPDATE project
    INNER JOIN schedule on project.site = schedule.site 
    SET project.i-date = MAX(schedule.f-date) as 'f-date', project.status = 'complete'
    WHERE project.site = 'site123'

but that didn't work. I suspect I need some different methods of joining but having trouble getting the right mixture.

Upvotes: 3

Views: 3025

Answers (2)

evilpenguin
evilpenguin

Reputation: 5478

How about a subselect?

UPDATE project
SET 
    project.`i-date` = (
        SELECT MAX(`f-date`)
        FROM schedule
        WHERE schedule.site = project.site
    ),
    project.status = 'complete'
WHERE project.site = 'site123'

Upvotes: 9

Priya Dadlani
Priya Dadlani

Reputation: 15

UPDATE table_1 set column_1=(select max(salary) as salary from table_2) where 1

Upvotes: -1

Related Questions