DELETE FROM with INNER JOIN doesn't work

[LucieLastic]

hi

What have I done wrong here?

delete FROM TestError
  inner join Test on Test.ID = TestError.ID
where Test.State = 1 

Test table contents

ID    State
1	1
2	NULL
3	1
4	2
5	3
6	3
7	2
8	1
9	NULL

TestError contents:

ID    Status
1	A
2	B
3	C
4	D
5	E
6	F
7	G
8	H
9	I

Many thanks in advance.
Lou

 

[gmmastros]

You need to identify the table that should be deleted from, like this:

delete [!]TestError[/!] FROM TestError
  inner join Test on Test.ID = TestError.ID
where Test.State = 1

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RiverGuy]

delete [red]TestError[/red] FROM TestError
  inner join Test on Test.ID = TestError.ID
where Test.State = 1

 

[LucieLastic]

Why do you have to specify it before and after the FROM bit?

 

[LucieLastic]

Thanks for putting me right, btw, guys, it's much appreciated.

 

[RiverGuy]

Why do you have to specify it before and after the FROM bit?

Because SQL wouldn't know which table you are talking about. You've got two tables: TestError and Test.

 

[gmmastros]

You need it in both places because the order it is listed in the FROM part does not matter. For example, the following two queries would be identical.

delete TestError FROM [blue]TestError[/blue]
  inner join [green]Test[/green] on Test.ID = TestError.ID
where Test.State = 1

delete TestError FROM [green]Test[/green]
  inner join [blue]TestError[/blue] on Test.ID = TestError.ID
where Test.State = 1

Since the order that you list the tables in the from clause does not matter, SQL cannot assign any significance to it. When there is ONLY ONE table in the from clause, you do not need to put the table name in the Delete Clause. Personally, I'm not a big fan of omitting optional keywords, so even for a single table delete, I put the table name in the delete clause:

[tt]
Delete TableName
From TableName
Where SomeIdValue = 1
[/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[LucieLastic]

I see, that makes perfect sense. Thanks again for the info.

Lou

 

[SQLSister]

If you use an alias on the tables inteh join, then you only need to put the alias after the DELETE keyword, like so:

delete TE 
--select *
FROM TestError  TE 
inner join Test T
    on T.ID = TE.ID
where T.State = 1

I find it is a good practice to do this, so you can't ever accidentally highlight only the first line and wham delete the whole table. I also put a commented out select statement in there as it is often helpful when writing amore complex delete to be able to see the records it will be deleting.


"NOTHING is more important in a database than integrity." ESquared