Trying to update a table with a value from the same table

Question

UPDATE seasons SET good=good+1 WHERE id = (SELECT id FROM seasons ORDER BY id DESC LIMIT 1)

When I run this command I get the error:

#1093 - Table 'seasons' is specified twice, both as a target for 'UPDATE' and as a separate source for data

Possible duplicate of [Table is specified twice, both as a target for 'UPDATE' and as a separate source for data in mysql](https://stackoverflow.com/questions/44970574/table-is-specified-twice-both-as-a-target-for-update-and-as-a-separate-source) — Stephen Kennedy, Jan 07 '18 at 18:54

score 2 · Accepted Answer · answered Jan 07 '18 at 18:53

2

This is a MySQL limitation.

One method is to use JOIN. However, for this it is probably better to use LIMIT and ORDER BY:

UPDATE seasons s 
    SET s.good = s.good + 1 
    ORDER BY id DESC
    LIMIT 1;

This does assume that id is not duplicated in the table, but that seems like a reasonable assumption for a column called id.

answered Jan 07 '18 at 18:53

Gordon Linoff

I read about the workaround of using an alias for the table in the update part of the query to fix the issue. Is it a valid option as far as you know? Never tried since I never had the issue before – Lelio Faieta Jan 07 '18 at 19:02

1 Answers1