Thread: BUG #4295: DELETE WHERE Condition

The following bug has been logged online:

Bug reference:      4295
Logged by:          Fred Perniss
Email address:      perniss@zks-gmbh.de
PostgreSQL version: all Versions
Operating system:   Open SuSe Linux 11
Description:        DELETE WHERE Condition
Details:

Fist: Sorry for my bad english.

I have 2 tables

table t1 with fields t1.t1_id, t1.t1_value

table t2 with fields t2.t2_id, t2.t2_t1_id, t2.t2_value

table t2 have a forign key to table t1 (t2_t1_id)

I have to delete all rows from table t2 with the condition t1.t1_value = 5.

In Postgres the switch add_mising_from=on the SQL-Command

DELETE FROM t2
WHERE (t2.t2_t1_id=t1.t1_id)
AND t1.t1_inhalt=5
;

works (with message in Log).

If the switch add_missing_from=off following Error-Message will be promted:

Fehlender Eintrag in FROM Klausel für Tabelle t1

The new Syntax from SQL in MySQL Version 4

DELETE FROM T2 FROM t1, t2
WHERE (t2.t2_t1_id=t1.t1_id)
AND t1.t1_inhalt=5
;

is not implemented.

I don't find any other Syntax for this and I don't find any in the log, how
I can change the sytax or how postgre do this if the switch add_mising_from
is on.

Can you tell me the correct syntax or can you tell me how the server
transform the query if the switch is set to on?

Am Freitag, 11. Juli 2008 schrieb Fred Perniss:
> In Postgres the switch add_mising_from=on the SQL-Command
>
> DELETE FROM t2
> WHERE (t2.t2_t1_id=t1.t1_id)
> AND t1.t1_inhalt=5
> ;
>
> works (with message in Log).
>
> If the switch add_missing_from=off following Error-Message will be promted:
>
> Fehlender Eintrag in FROM Klausel für Tabelle t1

You want to use DELETE .. USING.  See the man page for details.