469,927 Members | 1,403 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Deleting with LEFT JOIN=>How to?

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

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

Thanks for your help!
Jul 20 '05 #1
2 1570
R. Tarazi wrote:
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

=> 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/
Jul 20 '05 #2
> 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 FROM platzierungen LEFT JOIN firmendaten ON
firmendaten.firmendatenid = platzierungen.firmendatenid WHERE
firmendaten.firmendatenid IS NULL


what about this:

Delete From platzierungen
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
HP
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Federico Moschini [328594] | last post: by
2 posts views Thread by R. Tarazi | last post: by
2 posts views Thread by Bart Plessers \(artabel\) | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.