Solved

Query Syntax - Update

Posted on 2014-07-17
12
204 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
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 goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…

929 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

10 Experts available now in Live!

Get 1:1 Help Now