CODEWITHSUNDEEP
mysqlsqlsql-update
Jordi Serra
Jordi Serra

Reputation: 132

Update column same table

I have a problem when I try to update a value in a row database. I need update (add 1 hour) to especifics users in a USER table.

SELECT DATE_ADD(dateOfBirth, INTERVAL 1 HOUR), dateOfBirth 
from USER 
where dateOfBirth like '%23:00:00%';

But when I execute the next UPDATE, show a error

UPDATE USER
set dateOfBirth = DATE_ADD(dateOfBirth, INTERVAL 1 HOUR)
where id in (select u.id FROM USER u where u.dateOfBirth like '%23:00:00%');

Error:

Error Code: 1093. You can't specify target table 'USER' for update in FROM clause

It's possible execute this sentence with other way?

Upvotes: 1

Views: 79

Answers (3)

BlackSwan
BlackSwan

Reputation: 384

The subquery in the update is not required.

UPDATE USER
set dateOfBirth = DATE_ADD(dateOfBirth, INTERVAL 1 HOUR)
where dateOfBirth like '%23:00:00%';

Upvotes: 3

Richard Hansell
Richard Hansell

Reputation: 5403

What's wrong with:

UPDATE USER
SET dateOfBirth = DATE_ADD(dateOfBirth, INTERVAL 1 HOUR)
WHERE dateOfBirth like '%23:00:00%';

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Isn't the id unique in the table? If so:

update user u
    set dateOfBirth = DATE_ADD(dateOfBirth, INTERVAL 1 HOUR)
where time(u.dateOfBirth) = '23:00:00';

Note that the dateOfBirth is apparently a date/time column (why it contains a time I don't understand). So, date/time functions are more appropriate for the comparison.

Upvotes: 1

Related Questions