SQL Syntax
This select query is correct. I need to update j.LAST_WORK in the job table with the results from the query.
SELECT j.JOB, MAX(l.DATE_WORK) 'LAST DAY WORKED'
FROM job j, labor l
WHERE j.JOB_ID = l.JOB_ID
AND j.JSTATUS = 'I'
AND j.DELETED = 'N'
AND l.DELETED = 'N'
GROUP BY j.JOB_ID;
SELECT j.JOB, MAX(l.DATE_WORK) 'LAST DAY WORKED'
FROM job j, labor l
WHERE j.JOB_ID = l.JOB_ID
AND j.JSTATUS = 'I'
AND j.DELETED = 'N'
AND l.DELETED = 'N'
GROUP BY j.JOB_ID;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"couple of typos"?
Anyway, you got the idea and that is what we aim at. Thanks for the quick closure that's always appreciated.
Cheers, Paul
do you mean the lower case job in line 1?
as you didn't identify the field to update I did say I had made a guess, so the difference in the last line isn't a typo - is was an assumed field name.
I cannot see any other differences. Are there any others?
as you didn't identify the field to update I did say I had made a guess, so the difference in the last line isn't a typo - is was an assumed field name.
I cannot see any other differences. Are there any others?
Anyway, you got the idea and that is what we aim at. Thanks for the quick closure that's always appreciated.
Cheers, Paul
ASKER
UPDATE job AS target
INNER JOIN (
SELECT j.JOB_ID, MAX(l.DATE_WORK) AS LAST_DAY_WORKED
FROM job j
INNER JOIN labor l ON j.JOB_ID = l.JOB_ID
WHERE j.JSTATUS = 'I'
AND j.DELETED = 'N'
AND l.DELETED = 'N'
GROUP BY j.JOB_ID
) AS source
ON target.JOB_ID = source.JOB_ID
SET target.LAST_WORK = source.LAST_DAY_WORKED;