Solved

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

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

757 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

20 Experts available now in Live!

Get 1:1 Help Now