Solved

Query Syntax - Update

Posted on 2014-07-17
12
220 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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 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 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Part One of the two-part Q&A series with MalwareTech.
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

630 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