CODEWITHSUNDEEP
sqlsql-serverjoinsql-delete
mikec
mikec

Reputation: 165

SQL : Deleting records found using 3 joins

I am using SQL Server 2014 database, and SQL Server Management Studio to create and run queries.

Tables are :

Persons

| ID | personName |
+----+------------+
| 1  |    Hamish  |
| 2  |    Morag   |
| 3  |    Ewan    |

Cars

| ID | CarName |
+----+---------+
| 1  |  Humber |
| 2  |  Austen |
| 3  |  Morris |

Gadgets

| ID | GadgetName |
+----+------------+
| 1  |  Cassette  |
| 2  |     CD     |
| 3  |   Radio    |

CarToPersonMap

| ID | CarID | PersonID |
+----+-------+----------+
| 1  |   1   |    1     |

CarToGadgetMap

| ID | CarID | GadgetID |
+----+-------+----------+
| 1  |   2   |    2     |

The map tables have the appropriate foreign keys.

I want to delete records where a Car exists but is unused. So in the example above I want to delete Car with ID = 3.

I have a SELECT statement that uses 3 JOINs as follows. The statement works and it returns the correct row(s), i.e. Car with ID = 3.

SELECT * 
FROM
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
     WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
     WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

When I attempt a DELETE using the code below, it deletes all 3 Car rows:

DELETE Cars 
FROM
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
     WHERE CarToGadgetMap.CarID IS NULL) t1
JOIN
    (SELECT Cars.*
     FROM Cars 
     LEFT JOIN PersonToCarMap ON Cars.ID = PersonToCarMap.CarID
     WHERE PersonToCarMap.CarID IS NULL) t2 ON t1.ID = t2.ID

Result message:

(3 row(s) affected)

and inspection shows all 3 rows in the Cars table have been deleted.

Why do all records get deleted, when the SELECT statement returns just 1 row?

Can anyone help, please?

Thanks in advance

Upvotes: 0

Views: 113

Answers (3)

Clifford Piehl
Clifford Piehl

Reputation: 535

Maybe use a WHERE clause instead of FROM.

DELETE cars WHERE cars.id = 3 (Get your query select only the id from your logic, rather than selecting all the tables with cars.*)

Upvotes: 0

Charlieface
Charlieface

Reputation: 71805

When you execute a DELETE or UPDATE statement, the table to modify is calculated as follows:

  • Look at the FROM clause (in DELETE this is the second FROM) for a top-level table aliased with that name.
  • Otherwise, look for a single top-level reference to Cars even with an alias
  • Otherwise cross-join the table Cars with the whole FROM clause

The problem with your DELETE is that the top-level scope has no table named or aliased as Cars, so it's in effect just doing a big cross-join on the whole Cars table.

You can do this instead like this

DELETE Cars
WHERE NOT EXISTS (SELECT 1
    FROM CarToPersonMap cpm ON Cars.ID = cpm.CarID)
  AND NOT EXISTS (SELECT 1
    FROM CarToGadgetMap cgm ON Cars.ID = cgm.CarID);

You can also specify explicitly a second FROM clause

DELETE Cars
FROM Cars
WHERE ...

Upvotes: 0

Raju Ahmed
Raju Ahmed

Reputation: 1268

You can do it by just using this code. I tested it by creating a database & data at my end.

 DELETE Cars
 FROM Cars 
 LEFT JOIN CarToPersonMap ON Cars.ID = CarToPersonMap.CarID
 LEFT JOIN CarToGadgetMap ON Cars.ID = CarToGadgetMap.CarID
 WHERE CarToPersonMap.CarID IS NULL and  CarToGadgetMap.CarID IS NULL

Upvotes: 2

Related Questions