Delete rows out of temp table - SQL Server Forums

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)

Delete rows out of temp table

Author

Topic

tjwent69
Starting Member

30 Posts

Posted - 2008-07-11 : 09:10:44

Basically I want to delete some rows out of a temp table. When I use Delete #MyTempTable Select 'query for list data' it deletes all rows. Which might be exactly what it is supposed to do.

If that is the case and I cannot delete rows out of a temp table, then I need help coming up with a way to solve my problem.

Here is the back story and subsequent problem.

I am purging data from my database. I have items that have not been used for 2 years. So they can be removed. I have a temp table which contains these items and their groups. From this item table I can get a list of the item groups which I put into another temp table. There are 831. But... I can only remove the item groups if 100% Of the items are also being removed. I can get this information and there are 673 of those 831 that cannot be removed. So in the temp table I only want 158 item groups. I want to delete the 673 out of the list of 831 but if I use delete #ItemGroupsToPurge Select 'the 673 items' it deletes all of the rows.

What I really want to do is write a query that only lists the 158 groups. But my brain is stuck with my first solution.

Item Group Sub-Query to identify for purge.
19940 01M 19940
19340 01M 19340
18940 01M 18940
19040 01M 19040
40380 04A NULL
40240 04A NULL
40140 04A NULL
40340 04A NULL
40440 04A NULL
50140 05A NULL
58240 05D 58240
58360 05D 58360
58140 05D 58140
50240 05D NULL
58040 05D NULL

so 04A items are not going to be purged at all. 05D has some items to purge 01M has all items going to be purged. Only 01M should be in the list. I have 01M and 05D in my temp table. I can get 05D in a list to delete. What I need is only 01M and populate the temp table with that.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-11 : 09:24:23

you can delete from a temp table it's just your syntax that's wrong. I'm confused by your explanation though? see this as a simple example...


--drop table #test1
create table #test1 (col1 int)
insert into #test1
select 1 union
select 2 union 
select 3 union
select 4

--drop table #test2
create table #test2 (cola int)
insert into #test2
select 1 union
select 2

delete from #test1
where col1 in (select cola from #test2)

select * from #test1

/* result
col1
-----------
3
4
*/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 00:25:07

I think seeing the sample data you posted what you want is to delete the records from temp table which have the thrid field as NULL.I dnt know how you will be getting this result. If you can provide me with query for that, i can show you the full delete.

tjwent69
Starting Member

30 Posts

Posted - 2008-07-15 : 08:58:58

I will create another temp with the list of cannot delete and then delete like the example. Thanks so much. Since I was asked here are the query's I wrote. If they can be improved let me know. I am a program department of 1 and get stuck in just doing what I know to get the job done.

Populate the list -

Insert #ItemsToPurge

select
i.ItemNumber
, i.ItemGroupID
, i.BrandID

from udp_Item i left join
-- Get Sales for the past 24 months
(SELECT oi.ItemNumber, SUM(ActualQuantitySold) As QTy
FROM dbo.udp_OrderItem oi Inner Join
udp_Order o ON o.OrderID = oi.OrderID
WHERE oi.InvoiceDate >= dateadd(mm,-24,'07/11/2008') and oi.InvoiceDate < '07/11/2008'
and oi.OrderTypeID in ('IN','CM','CA','DA','PO','RO','SU','TO')
and o.IsCancelled = 0
Group by oi.ItemNumber
) as Invoice ON Invoice.ItemNumber = i.ItemNumber
Where i.ItemNumber < '979999'
and Invoice.ItemNumber is null
and i.BottleQuantityOnHand = 0
-- If there are no invoices and no QuantityOnHand it can be deleted

Simple. What I have now is a temp table with all the items that fit my rules.

I load the ItemsGroups into another temp table. (The possible delete list)

Here is my query for getting the list of cannot deletes.

Select distinct
i.ItemGroupID, i.ItemNumber, ItemsToPurge.ItemNumber
From udp_Item I Left Outer Join
-- Get the purge list
(SELECT ItemNumber
FROM #ItemsToPurge) as ItemsToPurge on ItemsToPurge.ItemNumber = i.ItemNumber
Where ItemsToPurge.ItemNumber is NULL
-- If an item is not in the purge list it cannot be deleted.

I start with the 5000+ items in the dB and if it is not in the purge list then I cannot delete it no matter what.

Thanks for the help

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 09:13:12

[code]DECLARE @Sample TABLE (Item INT, Grp CHAR(3), SubQuery INT)

INSERT @Sample
SELECT 19940, '01M', 19940 UNION ALL
SELECT 19340, '01M', 19340 UNION ALL
SELECT 18940, '01M', 18940 UNION ALL
SELECT 19040, '01M', 19040 UNION ALL
SELECT 40380, '04A', NULL UNION ALL
SELECT 40240, '04A', NULL UNION ALL
SELECT 40140, '04A', NULL UNION ALL
SELECT 40340, '04A', NULL UNION ALL
SELECT 40440, '04A', NULL UNION ALL
SELECT 50140, '05A', NULL UNION ALL
SELECT 58240, '05D', 58240 UNION ALL
SELECT 58360, '05D', 58360 UNION ALL
SELECT 58140, '05D', 58140 UNION ALL
SELECT 50240, '05D', NULL UNION ALL
SELECT 58040, '05D', NULL

SELECT *
FROM @Sample

-- Get records where there is no NULL record for Grp
SELECT s.Item,
s.Grp,
s.SubQuery
FROM @Sample AS s
LEFT JOIN (
SELECT Grp
FROM @Sample
WHERE SubQuery IS NULL
) AS x ON x.Grp = s.Grp
WHERE x.Grp IS NULL

-- Get records where there is only NULL record for Grp
SELECT s.Item,
s.Grp,
s.SubQuery
FROM @Sample AS s
INNER JOIN (
SELECT Grp
FROM @Sample
GROUP BY Grp
HAVING MIN(CASE WHEN SubQuery IS NULL THEN 1 ELSE 0 END) = 1
) AS x ON x.Grp = s.Grp[/code]

E 12°55'05.25"
N 56°04'39.16"

tjwent69
Starting Member

30 Posts

Posted - 2008-07-15 : 10:01:35

Thanks, you answered my question on how to only get the totally NULL groups in a temp table. This will be useful for some other things I have brewing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources