Deleting with LEFT JOIN=>How to?

[R. Tarazi]

Hello,

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

platzierungen
-----------------
platzierungenid
platzierungswor t
platzierungsfol ge
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.platzierungen id, a.platzierungsw ort, 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.fir mendatenid = platzierungen.f irmendatenid WHERE
firmendaten.fir mendatenid 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.fir mendatenid = platzieru

=> What am I doing wrong? How do I transfer above SELECT statement to
a DELETE Statement?

Thanks for your help!

[Chris Hope]

R. Tarazi wrote:

Hello,

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

platzierungen
-----------------
platzierungenid
platzierungswor t
platzierungsfol ge
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.platzierungen id, a.platzierungsw ort, 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.fir mendatenid = platzierungen.f irmendatenid WHERE
firmendaten.fir mendatenid 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.fir mendatenid = platzieru

=> What am I doing wrong? How do I transfer above SELECT statement to
a DELETE Statement?

Thanks for your help!

This article should help you:
http://www.electrictoolbox.com/artic...-table-delete/

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Peter]

> SELECT a.platzierungen id, a.platzierungsw ort, a.firmendatenid ,

b.firmendatenid
FROM platzierungen a
LEFT JOIN firmendaten b ON b.firmendatenid = a.firmendatenid
WHERE b.firmendatenid IS NULL
DELETE FROM platzierungen LEFT JOIN firmendaten ON
firmendaten.fir mendatenid = platzierungen.f irmendatenid WHERE
firmendaten.fir mendatenid IS NULL

what about this:

Delete From platzierungen
SELECT a.platzierungen id, a.platzierungsw ort, a.firmendatenid ,
b.firmendatenid
FROM platzierungen a
LEFT JOIN firmendaten b ON b.firmendatenid = a.firmendatenid
WHERE b.firmendatenid IS NULL
HP