Solved

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

Posted on 2008-10-17
6
907 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
[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
  • 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

Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

Question has a verified solution.

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

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

617 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