Solved

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

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

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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