Need Help on Query Without Cursor

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

SQL Server 2005 Forums

Transact-SQL (2005)

Need Help on Query Without Cursor

Author

Topic

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 17:31:30

the following query resides in a cursor. i am eliminating the cursor, and would like to perform this operation in a batch. is there a way to re-write this without the subquery and for all containers? so i want to get rid of all the variables here.

-- cursor goes here
-- drops value into @CurrentContainer

delete dbo.EDIShipmentStatuses
where ShipmentStatusCode not in (
select ShipmentStatusCode
from dbo.EDIShipmentStatusesPrep
where ContainerId = @CurrentContainer
and TerminalId = 7
)
and ContainerId = @CurrentContainer
and TerminalId = 7

-- end cursor

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 17:57:06

this is the best i've got so far:


delete  dbo.EDIShipmentStatuses
from	dbo.EDIShipmentStatuses
where	TerminalId = 7
		and ShipmentStatusCode not in (
	select	SS.ShipmentStatusCode
	from	dbo.EDIShipmentStatuses SS
			inner join dbo.EDIShipmentStatusesPrep SSP on SSP.ContainerId = SS.ContainerId and SS.TerminalId = SSP.TerminalId and SS.ShipmentStatusCode = SSP.ShipmentStatusCode
)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 17:57:09

DELETE e
FROM dbo.EDIShipmentStatuses e
LEFT OUTER JOIN ShipmentStatusCode s
ON e.ShipmentStatusCode = s.ShipmentStatusCode AND e.TerminalId = s.TerminalId
WHERE e.ShipmentStatusCode IS NULL AND e.TerminalId = 7

I think I've got the NULL and OUTER JOIN part correct. It's late on a Friday though and my mind isn't working great.

Tara Kizer

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 17:58:13

heheh i'll check it out.. that's the problem i have too.. brain... slow

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 18:07:33

nope that didnt' quite work. my previous reply does work, so i think i'll just stick with teh sub query for now. thanks for your help

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 18:10:26

actually, mine doesn't work. the outer delete query won't be in synch with the sub-query.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 18:13:30

If you could show us sample data for both tables and what should be deleted from those tables, we can probably more easily come up with a solution. I was just rewriting your query to how I thought it worked. A data example would make it so much clearer. I'm leaving in a few minutes though, so I may not be able to get to this until next Tuesday (we've got a holiday on Monday). Perhaps someone else will pop in and help out in the meantime though.

Tara Kizer

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 18:27:47

ok, i'll give an analogy that hopefully helps. so i've got a table of products, a status table which has multiple statuses for each product, and a new status table that has new statuses for the products.

3 tables:
Products
CurrentStatuses
NewStatuses

So Product 1, might have statuses A, B, and C in the CurrentStatuses table. Now in the NewStatuses table, Product 1 has statuses A and B. I want to remove C from the CurrentStatuses table.

BaggaDonuts
Yak Posting Veteran

52 Posts

Posted - 2007-02-16 : 18:55:51

well in an ugly, twisted way, this works i think... but i'm not enthused about it.


delete	dbo.EDIShipmentStatuses
from	dbo.EDIShipmentStatuses SS
		inner join dbo.EDIShipmentStatusesPrep SSP 
			on SSP.ContainerId = SS.ContainerId 
			and SS.TerminalId = SSP.TerminalId 
where	SS.ShipmentStatusCode not in (
			select	ShipmentStatusCode
			from	dbo.EDIShipmentStatusesPrep SSP2 
			where	SSP2.ContainerId = SS.ContainerId 
					and SS.TerminalId = SSP2.TerminalId
		)
		and SS.TerminalId = 7

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-02-16 : 20:32:07

DELETE c
FROM CurrentStatuses c
LEFT OUTER JOIN NewStatuses n
ON c.Status = n.Status
WHERE n.Status IS NULL

Based upon your analogy, the above should work. So in my first query, I think just my WHERE clause was incorrect.

To get a more accurate answer (if this isn't it), you really need to provide sample data for each table involved in your query. Show us perhaps 5 rows in each table and highlight the row(s) that should get deleted.

It is much easier for us to write queries by looking at data in tables rather than reading an explanation.

Tara Kizer

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources