Solved

You can't specify target table 'CLK_DATA' for update in FROM clause

Posted on 2008-10-17
6
879 Views
Last Modified: 2012-05-05
Hi we are migrating databse from mssql to mysql.
i have a query in mssql like this
UPDATE CLK_DATA SET STATUS=3
WHERE CLIENT_ID=1 AND (PAYROLL=18440) AND (TYPE = 'OF')AND (CLK_TIME = (
SELECT MIN(CLK_TIME)
FROM CLK_DATA WHERE CLIENT_ID=1 AND PAYROLL=18460)

when i try to execute this query i am getting below error message.
"You can't specify target table 'CLK_DATA' for update in FROM clause"

how do i solve this problem.
please do te need full
regards,
vijji
0
Comment
Question by:vijji_lakshmi
  • 3
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22739860
in MySQL, you cannot query from the same table that you update/insert.
you might try to create a view on the table, and query that view instead...
0
 
LVL 11

Accepted Solution

by:
miqrogroove earned 500 total points
ID: 22750365
Hi vijji_lakshmi, I suggest the fastest solution might be to write two queries that use a temporary table.

CREATE TEMPORARY TABLE CLK_TEMP
  SELECT MIN(CLK_TIME) AS CLK_MIN
  FROM CLK_DATA
  WHERE CLIENT_ID=1 AND PAYROLL=18460;

UPDATE CLK_DATA SET STATUS=3
WHERE CLIENT_ID=1 AND PAYROLL=18440 AND TYPE = 'OF' AND CLK_TIME=(
  SELECT CLK_MIN
  FROM CLK_TEMP
);

I need to point out three important things though.

#1  You have two different WHERE conditions (the PAYROLL values are different).  Is this intentional?

#2  Using two queries means this will not be an atomic database procedure.  Depending on the circumstances, you might want to wrap these queries inside a transaction.

#3  The temporary table is deleted when the MySQL connection is dropped.  If you need to run this more than once then you will need to tweak the CREATE statement a bit to overwrite values as needed.

Enjoy!
0
 

Author Comment

by:vijji_lakshmi
ID: 22764522
hey we can do this way also...
UPDATE customer_address SET default_address =1 WHERE address_id = (  SELECT MIN( address_id )FROM ( SELECT *FROM customer_address) AS tempWHERE customer_id =5 )
just for your information
Regds,
vijji
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22764575
vijji_lakshmi, I understand that to be correct as well.  Aliasing the updated table for a subquery is legal in MySQL.
0
 
LVL 11

Expert Comment

by:miqrogroove
ID: 22965515
Hi angelIII and vijji_lakshmi,
The temporary table solution should work and solves the problem of the target table error in MySQL.  Two queries are appropriate because the UPDATE syntax documentation for MySQL says, "Currently, you cannot update a table and select from the same table in a subquery."
I am still interested in answering this question if I can be of more help.
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

910 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

22 Experts available now in Live!

Get 1:1 Help Now