Search code examples
sqlmysqlmysql-error-1093

MYSQL nested query newbie question

I am trying to increment 'sellingDate' field that's in 'company' table.

UPDATE company 
   SET sellingDate = ((SELECT DATE_ADD((SELECT sellingDate 
                                          FROM company 
                                         WHERE cid = '35'), INTERVAL 1 DAY))) 
 WHERE cid = '35';

This query gives me an error saying:

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

What am I doing wrong here?

Solution

  • Use:

    UPDATE company 
   SET sellingDate = DATE_ADD(sellingDate, INTERVAL 1 DAY)
 WHERE cid = '35'

    MySQL doesn't allow a subquery in an UPDATE statement against the same table, but the subquery is unnecessary for this example. For some odd reason a self-join in an UPDATE statement won't return the 1093 error though it's the same logic.