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开发者_JAVA技巧 can't specify target table 'company' for update in FROM clause

What am I doing wrong here?

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.

Try:

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

You can not use sub query from same table which you are updating However you can achieve same by this query

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

Why do you need to use a nested query to increment this field?

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