Delete via Inner Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Delete via Inner Join

Author

Topic

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2005-12-11 : 05:54:45

This query below displays the required row, but I would like to delete a specific row by replacing SELECT with DELETE, It is not working any help ?

SELECT dbo.PropertyLocation.PropertyLocation, dbo.PropertyLocation.PropertyLocationID, dbo.PropertyLocality.PropertyLocality,
dbo.PropertyLocality.PropertyLocalityID, dbo.PropertyTown.PropertyTown, dbo.PropertyTown.PropertyTownID, dbo.PropertyArea.PropertyArea,
dbo.PropertyArea.PropertyAreaID, dbo.PropertyCountry.PropertyCountry
FROM dbo.PropertyLocation INNER JOIN
dbo.PropertyCountry ON dbo.PropertyLocation.PropertyCountryID = dbo.PropertyCountry.PropertyCountryID INNER JOIN
dbo.PropertyLocality ON dbo.PropertyLocation.PropertyLocationID = dbo.PropertyLocality.PropertyLocationID INNER JOIN
dbo.PropertyTown ON dbo.PropertyLocality.PropertyLocalityID = dbo.PropertyTown.PropertyLocalityID INNER JOIN
dbo.PropertyArea ON dbo.PropertyTown.PropertyTownID = dbo.PropertyArea.PropertyTownID
WHERE (dbo.PropertyArea.PropertyAreaID = @PropertyAreaID)

*************to************************

DELETE dbo.PropertyLocation.PropertyLocation, dbo.PropertyLocation.PropertyLocationID, dbo.PropertyLocality.PropertyLocality,
dbo.PropertyLocality.PropertyLocalityID, dbo.PropertyTown.PropertyTown, dbo.PropertyTown.PropertyTownID, dbo.PropertyArea.PropertyArea,
dbo.PropertyArea.PropertyAreaID, dbo.PropertyCountry.PropertyCountry
FROM dbo.PropertyLocation INNER JOIN
dbo.PropertyCountry ON dbo.PropertyLocation.PropertyCountryID = dbo.PropertyCountry.PropertyCountryID INNER JOIN
dbo.PropertyLocality ON dbo.PropertyLocation.PropertyLocationID = dbo.PropertyLocality.PropertyLocationID INNER JOIN
dbo.PropertyTown ON dbo.PropertyLocality.PropertyLocalityID = dbo.PropertyTown.PropertyLocalityID INNER JOIN
dbo.PropertyArea ON dbo.PropertyTown.PropertyTownID = dbo.PropertyArea.PropertyTownID
WHERE (dbo.PropertyArea.PropertyAreaID = @PropertyAreaID)

Kristen
Test

22859 Posts

Posted - 2005-12-11 : 06:04:20

Presuming that you want to delete the matching row(s) from the dbo.PropertyLocation table then you need:

DELETE D
dbo.PropertyLocation.PropertyLocation, dbo.PropertyLocation.PropertyLocationID, dbo.PropertyLocality.PropertyLocality,
dbo.PropertyLocality.PropertyLocalityID, dbo.PropertyTown.PropertyTown, dbo.PropertyTown.PropertyTownID, dbo.PropertyArea.PropertyArea,
dbo.PropertyArea.PropertyAreaID, dbo.PropertyCountry.PropertyCountry
FROM dbo.PropertyLocation AS D
INNER JOIN dbo.PropertyCountry ON dbo.PropertyLocation.PropertyCountryID = dbo.PropertyCountry.PropertyCountryID
INNER JOIN dbo.PropertyLocality ON dbo.PropertyLocation.PropertyLocationID = dbo.PropertyLocality.PropertyLocationID
INNER JOIN dbo.PropertyTown ON dbo.PropertyLocality.PropertyLocalityID = dbo.PropertyTown.PropertyLocalityID
INNER JOIN dbo.PropertyArea ON dbo.PropertyTown.PropertyTownID = dbo.PropertyArea.PropertyTownID
WHERE (dbo.PropertyArea.PropertyAreaID = @PropertyAreaID)

Kristen

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-11 : 12:43:02

what i don't understand is how can a delete syntax be so complex to people to understand?

1. do select of data you want to delete. there can be inner, left, cross, full joins... whatever you want.
2. be sure you selected the right data
3. replace the part select .... from with delete tableNameOrAliasYouWantToDeleteFrom from
4. use begin tran ... rollback for first run
5. when you're sure you deleted the right data replace the rollback with commit.
6. go for coffe with a smile

Go with the flow & have fun! Else fight the flow

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 2005-12-11 : 17:28:32

Hi Kristen it didn't work, this is a sample structure of the inner join
ProprtyCountry = England
PropertyLocation = London
PropertyLocality = West London
PropertyTown = Hammersmith/Fulham
PropertyArea = Shepherd Bush

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-12-11 : 17:48:33

DELETE D
FROM dbo.PropertyLocation AS D
INNER JOIN dbo.PropertyCountry ON dbo.PropertyLocation.PropertyCountryID = dbo.PropertyCountry.PropertyCountryID
INNER JOIN dbo.PropertyLocality ON dbo.PropertyLocation.PropertyLocationID = dbo.PropertyLocality.PropertyLocationID
INNER JOIN dbo.PropertyTown ON dbo.PropertyLocality.PropertyLocalityID = dbo.PropertyTown.PropertyLocalityID
INNER JOIN dbo.PropertyArea ON dbo.PropertyTown.PropertyTownID = dbo.PropertyArea.PropertyTownID
WHERE (dbo.PropertyArea.PropertyAreaID = @PropertyAreaID)

That DELETE will work provided you have the right SELECT. When you run the SELECT statement, does it return the right data?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-12-11 : 18:00:48

OBINNA_EKE:
go to My previous post and read it carefully

Go with the flow & have fun! Else fight the flow

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 00:52:01

>>Hi Kristen it didn't work,

Did you get Error?
Did that query delete unexpected rows?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources