CODEWITHSUNDEEP
mysqlsqlsql-update
Ivan
Ivan

Reputation: 37

MySQL Update subquery table is specified twice

I'm trying this subquery to decrease 1 in my quantity every time I execute this query. I think it will work on SQL-Server but why isn't it working on MySQL. I'm getting this error:

Table 't1' is specified twice, both as a target for 'UPDATE' and as a separate source for data

here's my code

Update tblbooks AS t1
set t1.Quantity = (Select t2.Quantity-1 
                   from tblbooks AS t2 
                   where t2.BookId = 123)
where t1.BookId = 123

Upvotes: 1

Views: 161

Answers (1)

Mureinik
Mureinik

Reputation: 311528

You don't need the subquery there - you can reassign a calculation on a column back to the same column:

UPDATE tblbooks
SET    Quantity = Quantity - 1
WHERE  BookId = 123

Upvotes: 3

Related Questions