sql delete query

Question

When I want to exec the following query:

DELETE 
   login,
   klantGegevens,
   orderGegevens
FROM login 
INNER JOIN klantGegevens ON login.klantid=klantGegevens.klantid 
INNER JOIN orderGegevens ON login.klantid=orderGegevens.loginNr 
WHERE login.klantId=3

I get the error:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

Obviously there is something wrong between "DELETE" and "INNER JOIN". (DELETE login,klantGegevens,orderGegevens FROM login INNER JOIN )

What should it be?

Thankyou.

EDIT :

enter image description here

I want to DELETE klantId 3.

Maybe this is some sql-server extension, but in normal SQL it doesn't make sense to delete individual columns. — Colonel Thirty Two, May 29 '15 at 18:09
`orderGegevensfrom` is missing something (specifically a space) — Mike G, May 29 '15 at 18:11
@Colonel Thirty I want to delete several rows from several tabels — Jenssen, May 29 '15 at 18:14
possible duplicate of [Is it possible to delete from multiple tables in the same SQL statement?](http://stackoverflow.com/questions/17539145/is-it-possible-to-delete-from-multiple-tables-in-the-same-sql-statement) and [How do I delete from multiple tables using INNER JOIN in SQL server](http://stackoverflow.com/questions/783726/how-do-i-delete-from-multiple-tables-using-inner-join-in-sql-server) — C-Pound Guru, May 29 '15 at 18:44

Chains · Answer 1 · 2015-05-29T19:06:21.787

I don't know if your attempt to use inner joins implies that you only want to delete a record if it exists in all 3 tables.

For this answer -- assuming you don't care (you just want to delete it wherever you find it):

CREATE PROC dbo.DeleteLotsOfStuff
(@id int)
AS

Begin

DELETE FROM login WHERE klantid = @id
DELETE FROM klantGegevens WHERE klantid = @id
DELETE FROM orderGegevens WHERE loginNr = @id

End

EXEC dbo.DeleteLotsOfStuff 3

Alternatively -- you can add constraints (primary key / foreign key relationship) to the main table, and then set it to `DELETE CASCADE'. If you do that, then whenever you run (for example):

DELETE FROM login WHERE klantid = @id

It would automatically delete the corresponding rows from klantGegevens and orderGegevens.

More information on adding contraints here: https://msdn.microsoft.com/en-us/library/ms188066.aspx

score 1 · Answer 2 · answered May 29 '15 at 18:09

1

Try this syntax

DELETE L
FROM   login L
       INNER JOIN klantGegevens
               ON L.klantid = klantGegevens.klantid
       INNER JOIN orderGegevens
               ON L.klantid = orderGegevens.loginNr
WHERE  L.klantId = 3

answered May 29 '15 at 18:09

Pரதீப்

91,748
19
131
172

thankyou for answering, but I already tried this and it doesn't delete anything. – Jenssen May 29 '15 at 18:14
Sample data and Expected result would certainly help. – Pரதீப் May 29 '15 at 18:15
of course please see my edit^ – Jenssen May 29 '15 at 18:21
@Jenssen - It is not possible to delete more than one table in a single delete query. You have to write individual delete queries to delete each table – Pரதீப் May 29 '15 at 18:25
But I want to delete one row in multiple tables, not delete the tables! I want to delete the row where klantid=3 (loginnr in ordergegeven is klantid) – Jenssen May 29 '15 at 18:28
2

Deleting the table in the sense i meant deleting the row only. To delete a single row from three different tables you need to write three different delete queries. – Pரதீப் May 29 '15 at 18:31
oke thanks for clearing that up. – Jenssen May 29 '15 at 18:32

sql delete query

2 Answers2