Solved

Query Syntax - Update

Posted on 2014-07-17
12
214 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article will show, step by step, how to integrate R code into a R Sweave document
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
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.

734 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