Solved

Query Syntax - Update

Posted on 2014-07-17
12
196 Views
Last Modified: 2014-07-18
This query returns the proper values...

                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
                ORDER BY LAST_DAY_WORKED;

When I run this update it doesn't update the LAST_WORK field.

    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;
0
Comment
Question by:hdcowboyaz
  • 7
  • 3
  • 2
12 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 40203869
It is a limitation of MySQL. Please check the official manual here (closer to the end):
Currently, you cannot update a table and select from the same table in a subquery.
You need to use a different syntax:
    UPDATE job AS target
    SET target.LAST_WORK =  (
                SELECT MAX(l.DATE_WORK) AS LAST_DAY_WORKED
                FROM job j
                INNER JOIN labor l ON j.JOB_ID = l.JOB_ID
                WHERE j.JOB_ID = target.JOB_ID
                AND j.JSTATUS = 'I'
                AND j.DELETED = 'N'
                AND l.DELETED = 'N'
               );

Open in new window

0
 

Author Comment

by:hdcowboyaz
ID: 40203871
So how can this be done?
0
 
LVL 24

Expert Comment

by:chaau
ID: 40203874
Have you checked my syntax? Doesn't it work? What error are you getting?
0
 

Author Comment

by:hdcowboyaz
ID: 40203877
Make a another job table with only JOB_ID and LAST_WORK?
0
 
LVL 24

Expert Comment

by:chaau
ID: 40203882
Try this one:
UPDATE job AS target
    SET target.LAST_WORK =  (
                SELECT MAX(l.DATE_WORK) AS LAST_DAY_WORKED
                FROM labor l 
                WHERE l.JOB_ID = target.JOB_ID
               )
WHERE target.JSTATUS = 'I'
                AND target.DELETED = 'N'
                AND target.DELETED = 'N';

Open in new window

0
 

Author Comment

by:hdcowboyaz
ID: 40203885
Sorry I didn't fully read your first response. The first query you sent returned this error.

<e>Query: UPDATE job AS target SET target.LAST_WORK = ( SELECT MAX(l.DATE_WORK) AS LAST_DAY_WORKED FROM job j INNER JOIN labor l ON j.JOB_...

Error Code: 1093
You can't specify target table 'target' for update in FROM clause

The second query returned this error.

<e>Query: UPDATE job AS target SET target.LAST_WORK = ( SELECT MAX(l.DATE_WORK) AS LAST_DAY_WORKED FROM labor l WHERE l.JOB_ID = target.JO...

Error Code: 1048
Column 'LAST_WORK' cannot be null
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:hdcowboyaz
ID: 40203908
If it helps

                AND j.DELETED = 'N'
                AND l.DELETED = 'N'

can be removed from the query.
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40203928
Error Code: 1048
Column 'LAST_WORK' cannot be null


Then that field has a NOT NULL condition applied to it but the inner subquery is returning NULLs (not sure why because I don't know your data).

So, try ensuring the inner subquery does not return NULLs

Please read this bit carefully! You will have to apply your own knowledge to this. I'm guessing

UPDATE job AS target
    SET target.LAST_WORK =  (
                SELECT MAX(l.DATE_WORK) AS LAST_DAY_WORKED
                FROM labor l 
                WHERE l.JOB_ID = target.JOB_ID
                AND l.DATE_WORK IS NOT NULL /* you need to consider this bit: find conditions that stop NULLs being returned */
               )
WHERE target.JSTATUS = 'I'
                AND target.DELETED = 'N' /* remove if you want to, your choice */
                AND target.DELETED = 'N' /* remove if you want to, your choice */
;

Open in new window

Because this is a complete guess I would prefer it if you didn't just report the next error, but gave such errors some thought. In many cases they aren't that hard to interpret.
0
 

Author Comment

by:hdcowboyaz
ID: 40203968
No errors, however the original query returned 170 values and your last query returned 229.
I checked three Jobs and the dates where the same in both queries.
For some reason it updated more rows, which may not be an issue.

I will check more in the morning

Thanks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40203987
OK. No errors is good.
Increase of records is counter-intuitive. perhaps by removing:
               AND j.DELETED = 'N'
                AND l.DELETED = 'N'

& I do hope you are doing this in a test environment.
0
 

Author Comment

by:hdcowboyaz
ID: 40203991
I left those two in, so results should be the same.  It is in a test environ
0
 

Author Closing Comment

by:hdcowboyaz
ID: 40205158
All good now
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
The viewer will learn how to clear a vector as well as how to detect empty vectors in C++.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now