Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Cartesian product Expand / Collapse
Author
Message
Posted Wednesday, October 24, 2012 10:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 12:24 AM
Points: 10, Visits: 113
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
Post #1376593
Posted Wednesday, October 24, 2012 1:42 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 2,665, Visits: 3,634
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #1376649
Posted Wednesday, October 24, 2012 3:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 04, 2013 12:24 AM
Points: 10, Visits: 113
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.

Post #1376703
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse