June 11, 2003 at 4:56 pm
I have this statement
DELETE #CL_FINAL
FROM (SELECT * FROM #CL_FINAL WHERE CorrectResponseID = 1) AS CL1,
(SELECT * FROM #CL_FINAL WHERE CorrectResponseID = 4) AS CL4
WHERE CL1.Address1 = CL4.Address1 AND CL1.CompanyID = CL4.CompanyID
but it deletes all the records from my temporarly #CL_FINAL table, what I'd want is for it to only delete the limited 332 records as displayed here:
SELECT *
FROM (SELECT * FROM #CL_FINAL WHERE CorrectResponseID = 1) AS CL1,
(SELECT * FROM #CL_FINAL WHERE CorrectResponseID = 4) AS CL4
WHERE CL1.Address1 = CL4.Address1 AND CL1.CompanyID = CL4.CompanyID
what did I miss?
Thanks
-Francisco
-Francisco
June 11, 2003 at 5:50 pm
maybe my post is confusing.. let me reword it... hmm,
you see the statement above (the one w/ the delete) is somehow not taking into consideration the where clause below it. I would have though that If I replace the SELECT * with a DELETE #CL_Final would only delete the same records pulled from the SELECT *, is this thinking wrong? is my syntax wrong... any pointers will help, I've checked BOL and that's how I came up with this statement.
-Francisco
-Francisco
June 12, 2003 at 9:52 am
I think that this might work:
DELETE #CL_FINAL
FROM #CL_FINAL
where CorrectResponseID = 1 or CorrectResponseID = 4
Here is how I would delete based on joins to other tables:
DELETE #CL_FINAL
FROM #CL_FINAL
join #anotherTable on #CL_FINAL.pk = #anotherTable.col2
join #yetanotherTable on #anotherTable.pk = #yetanotherTable.col2
Before I ever run this type of statement, I test what would be deleted with this:
--DELETE #CL_FINAL
select * FROM #CL_FINAL
join #anotherTable on #CL_FINAL.pk = #anotherTable.col2
join #yetanotherTable on #anotherTable.pk = #yetanotherTable.col2
June 12, 2003 at 9:55 am
Thank you that is a big help...this is the script I am gonna go with...
DELETE #CL_FINAL
FROM #CL_FINAL AS CL1 INNER JOIN (SELECT * FROM #CL_FINAL WHERE CorrectResponseID = 1) AS CL4
ON CL1.Address1 = CL4.Address1 AND CL1.CompanyID = CL4.CompanyID
WHERE CL1.CorrectResponseID = 4
which effectively deletes all CorrectResponseID 4's that have a corresponding record as a correctresponseID 1, (no need for both).
Thank you for your help... I was at wits ends, it seems all I had was a goofy join there.
-Francisco
-Francisco
June 12, 2003 at 3:33 pm
ok I think that I get you now...
join a table to itself on address and companyID
and delete rows where response is a 4 for one and a 1 for the other.
select * from CL as CL1
join CL as CL2 on CL1.address=CL2.address and CL1.companyID = CL2.companyID
where (CL1.response = 1 and CL2.response = 4)
/*This condition will double the rows returned.-->*/
--or (CL1.response = 4 and CL2.response = 1)
Yep makes sense.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply