Complex UPDATE goes wrong?!
Posted by:
qeldroma
Date: January 14, 2005 06:56AM
Hello,
i've got an UPDATE where i'll have to make a complicated "WHERE"-clause. For this clause i need to mention the tables from where i get the infos, i think. But UPDATE seems only to accept ONE table in the beginning, so it seems that it's not similar to the "FROM"-clause...
Error is only "Syntax-error 1064" without any further reason.
First the tables:
- accessproducts -
+----+----------------+-----------+------------+--------+
| id | name | bandwidth | freeVolume| isFlat |
+----+----------------+-----------+------------+--------+
| 1 | AccessStandard| 64 | 99 | true |
| 2 | AccessEnhanced| 256 | 99 | true |
| 3 | AccessProfi2 | 2562 | 99 | false |
+----+----------------+-----------+------------+--------+
- products -
+--+------------------+------------+----------------+-------------------+------------------+
| id | name |mandants_id |mailproducts_id |domainproducts_id |accessproducts_id |
+--+------------------+------------+----------------+-------------------+------------------+
| 1 | ProduktStandard | 1 | 1 | 1 | 1 |
| 2 | ProduktFamily | 1 | 2 | 1 | 1 |
| 3 | ProduktProfi | 1 | 3 | 3 | 3 |
+--+------------------+------------+----------------+-------------------+------------------+
- mandants -
+----+-----------------+----------+
| id | name | password |
+----+-----------------+----------+
| 1 | DemoMandant | |
| 2 | DemoMandant2 | |
+----+-----------------+----------+
My SQL-strings:
----------------------------------------------------------------------------------------
UPDATE accessproducts,products,mandants
SET accessproducts.name='AccessProfi2', accessproducts.bandwidth=2562,
accessproducts.freeVolume=99.0, accessproducts.isFlat='false'
WHERE mandants.name='DemoMandant' AND products.mandants_id=mandants.id
AND products.accessproducts_id=accessproducts.id AND accessproducts.name='AccessProfi' LIMIT 1;
----------------------------------------------------------------------------------------
Another failed tryout, seperating the WHERE-clause, was:
----------------------------------------------------------------------------------------
DROP tmp1;
CREATE TEMPORARY TABLE tmp1 TYPE=HEAP SELECT accessproducts.id FROM accessproducts,products,mandants WHERE mandants.name='DemoMandant' AND products.mandants_id=mandants.id AND products.accessproducts_id=accessproducts.id AND accessproducts.name='AccessProfi';
UPDATE accessproducts,tmp1 SET accessproducts.name='AccessProfi2', accessproducts.bandwidth=2562, accessproducts.freeVolume=99.0, accessproducts.isFlat='false' WHERE accessproducts.id=tmp1.id LIMIT 1;
----------------------------------------------------------------------------------------
As you can see, i only want to change the values in "accessproducts", but for this, i'll have to use other tables/columns in the "WHERE"-clause...
Of course i tried out the "WHERE"-part with an select to see if it works.
What's the "professionell" way for this?
Thanks, QD