1

I would like to mark rows as deleted when there are duplicate stock codes. This is my current query:

UPDATE stock s1
SET s1.deleted_at = now()
WHERE s1.product_id != (
    SELECT MIN(s2.product_id)
    FROM stock s2
    WHERE s2.stock_code = s1.stock_code
)

Error returned is:

SQL Error (1093): You can't specify target table 's' for update in FROM clause

I have tried wrapping it within another sub query but unable to get it working

mitkosoft
  • 5,221
  • 1
  • 12
  • 31
xylar
  • 6,975
  • 13
  • 52
  • 95
  • 1
    Use multiple-table UPDATE syntax (move subquery from WHERE to FROM in JOIN form). And replace `!=` condition with ` – Akina Feb 10 '20 at 11:02

2 Answers2

1

Mysql doesn't like that you reference the table that you want to upodate in the set. What you can do is this.

UPDATE stock s1
SET s1.deleted_at = now()
WHERE s1.product_id != (
    SELECT MIN(s2.product_id)
    FROM (SELECT product_id, stock_code FROM stock) s2
    WHERE s2.stock_code = s1.stock_code
)

More to that problem

xylar
  • 6,975
  • 13
  • 52
  • 95
nbk
  • 31,930
  • 6
  • 24
  • 40
  • Thanks that's working for me. I editted your answer to include stock_code in the sub query. – xylar Feb 10 '20 at 11:10
1

You can work around this issue with a multi-table UPDATE using a self-JOIN:

UPDATE stock s1
JOIN stock s2 ON s2.product_id < s1.product_id AND s2.stock_code = s1.stock_code
SET s1.deleted_at = NOW()
Nick
  • 123,192
  • 20
  • 49
  • 81