Solved

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

Posted on 2008-10-17
6
889 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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
phpMyAdmin simple sql statement 3 58
How to retrieve data related to last id inserted into database 3 57
[MYSQL]: Delete is very slow 4 70
mysql disables rename 4 70
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 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