MySQL Forums
Forum List  »  General

Re: Complex UPDATE goes wrong?!
Posted by: Jason Collison
Date: January 14, 2005 04:40PM

The way I'd _like_ to do this is with a simple subquery:

UPDATE accessproducts SET name='AccessProfi2'
WHERE id =
(SELECT a.id FROM accessproducts a, products p, mandants m
WHERE a.name='AccessProfi' AND p.accessproducts_id = a.id
AND m.id = p.mandants_id
AND m.name='DemoMandant');

But MySQL won't let you update tables listed in the subquery. Your second query where you separated the two was fine, except for the temp table stuff. It sounds like you're beating your head against the wall just to avoid saving a variable at the application level? That's how I'd do it. If you're worried about database consistency you can wrap lock statements around the two SQL queries.

If you REALLY _need_ to do this completely with SQL, you can create your tmp1 table and then do:

INSERT INTO tmp1 SELECT a.id FROM accessproducts a, products p, mandants m
WHERE a.name='AccessProfi' AND p.accessproducts_id = a.id
AND m.id = p.mandants_id
AND m.name='DemoMandant';

UPDATE accessproducts SET name='AccessProfi2'
WHERE id = (SELECT id FROM tmp1 LIMIT 1);

That works. (I did reverse the order of your joins but you get the idea)

Options: ReplyQuote


Subject
Written By
Posted
January 14, 2005 06:56AM
Re: Complex UPDATE goes wrong?!
January 14, 2005 04:40PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.