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!
Deleting with LEFT JOIN=>How to?
Moderator: General Moderators
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
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.
MySQL
http://dev.mysql.com/doc/mysql/en/EXIST ... eries.html
POSTGRES
http://www.postgresql.org/docs/7.4/inte ... query.html.
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-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
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!
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!
-
visionmaster
- Forum Contributor
- Posts: 139
- Joined: Wed Jul 14, 2004 4:06 am
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
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