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,262
  • 1
  • 13
  • 31
xylar
  • 7,433
  • 17
  • 55
  • 100
  • 1
    Use multiple-table UPDATE syntax (move subquery from WHERE to FROM in JOIN form). And replace `!=` condition with `<` - it will simplify the task avoiding GROUP BY. – 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
  • 7,433
  • 17
  • 55
  • 100
nbk
  • 45,398
  • 8
  • 30
  • 47
  • 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
  • 138,499
  • 22
  • 57
  • 95