Deleting with LEFT JOIN=>How to?
Posted: Thu Sep 02, 2004 2:05 am
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!
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!