SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Delete via Inner Join
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 12/11/2005 :  05:54:45  Show Profile

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

United Kingdom
22403 Posts

Posted - 12/11/2005 :  06:04:20  Show Profile
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
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 12/11/2005 :  12:43:02  Show Profile  Visit spirit1's Homepage
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

Edited by - spirit1 on 12/11/2005 13:04:21
Go to Top of Page

OBINNA_EKE
Posting Yak Master

234 Posts

Posted - 12/11/2005 :  17:28:32  Show Profile
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
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 12/11/2005 :  17:48:33  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
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.
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11750 Posts

Posted - 12/11/2005 :  18:00:48  Show Profile  Visit spirit1's Homepage
OBINNA_EKE:
go to My previous post and read it carefully

Go with the flow & have fun! Else fight the flow
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22744 Posts

Posted - 12/12/2005 :  00:52:01  Show Profile  Send madhivanan a Yahoo! Message
>>Hi Kristen it didn't work,

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

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000