Subqueries in MySQL

[Thomas Wuercher]

Hello!

Maybe somebody could give me a hint or workaround for a problem with the following statement:

update LAGERPLAETZE
  set FERTIGUNGSAUFTRAG=123
  where LAGERPLATZNUMMER in 
  (
      select min(LAGERPLATZNUMMER) from LAGERPLAETZE where
      FERTIGUNGSAUFTRAG=0
  )

What I' m obviously trying to do is first selecting a dataset with some initial-value and the lowest id and afterwards update this dataset.

The error-message is

'You can't specify target table 'lagerplaetze' for update in FROM clause'.

The MySQL-manual says it' s not allowed to specify the same table in the update- and subselect-statement, but what alternative have I got. The statement works with other databases like PostgreSQL pretty well.

In my application I therefore have to lock the table, select & update it, and afterwards unlock it again, but this seems pretty uncool to me, and I've got no ideas about possible side-effects on my app with competing access.

Thanks a lot in advance!

Best regards,
Thomas

 

[Luzie]

Hi,

use of a subselect is only possible starting from mysql-version 4.1

Du kannst Deine Fragen auch in Deutsch stellen.

 

[Thomas Wuercher]

Hallo, Luzie!

MySQL 4.1 habe ich installiert, nur findet sich in der Online-Doku unter Fehlerquellen folgender Eintrag:

"Incorrectly used table in subquery:

Error 1093 (ER_UPDATE_TABLE_USED)
SQLSTATE = HY000
Message = "You can't specify target table 'x'
for update in FROM clause"

This error occurs in cases like this:

UPDATE t1 SET column2 = (SELECT MAX(column1) FROM t1);

It's okay to use a subquery for assignment within an UPDATE statement, since subqueries are legal in UPDATE and DELETE statements as well as in SELECT statements. However, you cannot use the same table, in this case table t1, for both the subquery's FROM clause and the update target."

Tja, und genau dieses Szenario liegt in meinem Falle vor. Irgendwelche Ideen für einen Workaround (außer DB-Wechsel?).

Herzlichen Dank - auch für den sprachlichen Hinweis (.de sollte eigentlich zum .denken anregen).

LG
Thomas

 

[won_gak]

Hi Thomas,

in zu vielen Usergroups herumgewerkelt? *g*

Was du natürlich machen könntest:

SET @anything = (SELECT MAX(column1) FROM t1);
UPDATE t1 SET column2 = @anything;

Sind allerdings zwei Queries.