BUG #4295: DELETE WHERE Condition

Started by Fred Pernissalmost 18 years ago2 messagesbugs
Jump to latest
#1Fred Perniss
perniss@zks-gmbh.de

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?

#2Peter Eisentraut
peter_e@gmx.net
In reply to: Fred Perniss (#1)
Re: BUG #4295: DELETE WHERE Condition

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.