Page 1 of 1

Deleting with LEFT JOIN=>How to?

Posted: Thu Sep 02, 2004 2:05 am
by visionmaster
Hello,

DB-structure:
=========
firmendaten
-----------------
firmendatenid <-
firmennummer
name
strasse
hausnr

platzierungen
-----------------
platzierungenid
platzierungswort
platzierungsfolge
firmendatenid <-


Questions:
======
1.) I would like to find the records from the table 'positioning' that are not joined with 'companydata'. I did this as following, is that correct?

SELECT a.platzierungenid, a.platzierungswort, a.firmendatenid,
b.firmendatenid
FROM platzierungen a
LEFT JOIN firmendaten b ON b.firmendatenid = a.firmendatenid
WHERE b.firmendatenid IS NULL

2.) Now I want to delete just these found records, in the table 'platzierungen' that don't have a equivalent record in the table 'firmendaten', since the company does not existent anymore.

MySQL 4.0.20:
-----------------

DELETE FROM platzierungen LEFT JOIN firmendaten ON
firmendaten.firmendatenid = platzierungen.firmendatenid WHERE
firmendaten.firmendatenid IS NULL

MySQL error output:

#1064 - You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'LEFT JOIN firmendaten ON firmendaten.firmendatenid = platzieru


Thanks for your help!

Posted: Thu Sep 02, 2004 3:03 am
by CoderGoblin
For updates/deletion you need to use the keyword EXISTS.

MySQL
http://dev.mysql.com/doc/mysql/en/EXIST ... eries.html

POSTGRES
http://www.postgresql.org/docs/7.4/inte ... query.html.

Posted: Thu Sep 02, 2004 4:21 am
by ol4pr0
something like this should work

Code: Select all

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id
or:
DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

Posted: Thu Sep 02, 2004 4:22 am
by visionmaster
Thanks for the link. But I don't know how to transfer the information to my problem.

DELETE FROM platzierungen WHERE EXISTS(SELECT a.platzierungenid, a.platzierungswort, a.firmendatenid,
b.firmendatenid
FROM platzierungen a
LEFT JOIN firmendaten b ON b.firmendatenid = a.firmendatenid
WHERE b.firmendatenid IS NULL);


=>Or this totally wrong?

Thanks!

Posted: Mon Sep 06, 2004 2:55 am
by visionmaster
Hello,

Found the solution:

SELECT a.platzierungenid, a.platzierungswort, a.firmendatenid, b.firmendatenid
FROM platzierungen a
LEFT JOIN firmendaten b ON b.firmendatenid = a.firmendatenid
WHERE b.firmendatenid IS NULL

DELETE platzierungen.*
FROM platzierungen a
LEFT JOIN firmendaten b
ON a.firmendatenid = b.firmendatenid
WHERE b.firmendatenid is null

Check out this page:
http://www.electrictoolbox.com/article/ ... ble-delete