MYSQL nested query newbie question

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?

Thank you everyone for response.

m0dE
– m0dE

2011-01-26 06:04:34 +00:00
Commented Jan 26, 2011 at 6:04 — m0dE
– m0dE, Commented Jan 26, 2011 at 6:04

OMG Ponies · Accepted Answer · 2011-01-26 05:55:43Z

2

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.

answered Jan 26, 2011 at 5:55

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

jdev · Accepted Answer · 2011-01-26 05:56:11Z

0

Try:

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

answered Jan 26, 2011 at 5:56

jdev

7293 gold badges13 silver badges36 bronze badges

Comments

shankhan · Accepted Answer · 2011-01-26 05:56:51Z

0

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';

answered Jan 26, 2011 at 5:56

shankhan

6,5812 gold badges22 silver badges22 bronze badges

Comments

Olhovsky · Accepted Answer · 2011-01-26 05:59:23Z

0

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';

answered Jan 26, 2011 at 5:59

Olhovsky

5,5993 gold badges40 silver badges48 bronze badges

Collectives™ on Stack Overflow

MYSQL nested query newbie question

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related