Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Query Syntax - Update

Posted on 2014-07-17
12
Medium Priority
?
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 3
  • 2
12 Comments
 
LVL 25

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 25

Expert Comment

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

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

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
 

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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
This video teaches viewers about errors in exception handling.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

715 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