October 24, 2012 at 10:54 am
Hi,
I am wondering if the following query is correct. I have tested it on a small record set, and I get the correct result, but I am worry if the query could lead to unwanted Cartesian product:
declare @wksInPse as table
(
[WorkstationId] [int] ,
[WorkstationGuid] [uniqueidentifier]
)
delete from tbl_Workstations
where WorkstationGuid in (
select WorkstationGuid
from @wksInPse pse
where tbl_Workstations.WorkstationId <> pse.WorkstationId );
The column WorkstationId in the table wksInPse is populated from another fixed table, where this column is a Primary Key.
The column WorkstationId in the table tbl_Workstations is a Primary Key column. Does the clause WHERE need to be extended to look like:
WHERE tbl_Workstations.WorkstationGuid = pse.WorkstationGuid
AND tbl_Workstations.WorkstationId <> pse.WorkstationId
Thanks for ideas.
Lubomir
October 24, 2012 at 1:42 pm
What exactly are you trying to accomplish? Delete any workstation ID's that dont exist in your table?
If so ....
DELETE FROM W
FROM tbl_Workstations AS W
LEFT JOIN @wksInPse AS pse
ON W.WorkstationId = pse.WorkstationId
WHERE pse.WorkstationID IS NULL
If not, then we need more info.
Future note, read the article in my signature, and you'll get more timely and accurate responses.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 24, 2012 at 3:56 pm
It is expected to delete workstations from the first table, which have the same Guid but different Id in the second table.
The script is an extract from the bigger script. I just want to confirm what this part of the SQL is really doing.
In the mantime I was able to analyse the execution plan and the predicate generated by the SQL, and it looks like that script is working properly, i.e. uses the where clause
with '=' condition for Guid and <> condition for ID, even the first condition is not explicitelly stated in the source script.
As far as an ugly formating in the posting: I apologize for that, but I still don't know how to embeded a nice formated SQL statement into the post. To make it even more confusing, the Web UI shows the post formated correctly during the posting, but the result, after posting, is unformated text.
Thank you for your time.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply