CODEWITHSUNDEEP
mysqlsqlsubquery
saulob
saulob

Reputation: 645

MySQL update using subquery, error 1093

I tried searching here, but no luck. Nothing appear to be the same problem.

I have this select:

SELECT a.whse, a.sku, a.data, a.dt_exp FROM volume_hist_fin a
WHERE a.data = (
  SELECT MAX(b.data)
  FROM volume_hist_fin b
  WHERE b.md5_skuwhse = a.md5_skuwhse
 )
GROUP BY whse, sku, a.data

It works. I'm trying now to do the update. This update:

UPDATE volume_hist_fin a SET a.dt_exp = '2013-10-09'
WHERE a.data = (
  SELECT MAX(b.data) 
  FROM volume_hist_fin b
  WHERE b.md5_skuwhse = a.md5_skuwhse
 )

But I'm getting the error:

1093 "You can't specify target table 'a' for update in FROM clause"

All the problem it's that the subquery need to get the max data using the same id code (md5_skuwhse)

How can I make this update work?

Upvotes: 0

Views: 371

Answers (1)

peterm
peterm

Reputation: 92785

You can do it with JOIN

UPDATE volume_hist_fin a JOIN
(
  SELECT md5_skuwhse, MAX(data) max_data
    FROM volume_hist_fin
   GROUP BY md5_skuwhse
) b
    ON a.md5_skuwhse = b.md5_skuwhse
   AND a.data = b.max_data
   SET a.dt_exp = '2013-10-09'

Upvotes: 3

Related Questions