Delete from table variable with join

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 2000 Forums

Transact-SQL (2000)

Delete from table variable with join

Author

Topic

pOrmsby
Starting Member

12 Posts

Posted - 2007-10-09 : 08:50:02

Hi Team,

I am having difficultly with what I thought was an easy sql statement.

Within a stored proc. I create a table variable like so:

declare @Result TABLE (listid int, score bigint)

I insert into the table various collections of data, which work fine. When I am finished populating @Result, the data looks like this:

@Result:

listid  score
------------------
1805    1000
4302    500
4322    500
3211    1000
etc.

Each listid within @Result will also appear in a table called listcountry, and may have many entries. The listcountry table data can look like the following:

listcountry:

listID   CountryID
------------------
1805     225
1805     155
1805     41
4302     15
4322     15
4322     22
4322     225

I want to remove the records from @Result if none of the joined up countryID match a given value.

The value is supplied via a stored proc parameter - @ci int (value of 225)

I have tried the following:

DELETE r
FROM @Result r INNER JOIN ListCountry ON r.ListId = ListCountry.ListId
WHERE ListCountry.CountryId <> @ci

But this seems to remove the corresponding records in @result alltogether.

Any ideas? Your help will be much appreaciated.

Many thanks

Peter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 08:59:51

[code]-- Prepare sample data
DECLARE @Result TABLE (ListID INT, Score BIGINT)

INSERT @Result
SELECT 1805, 1000 UNION ALL
SELECT 4302, 500 UNION ALL
SELECT 4322, 500 UNION ALL
SELECT 3211, 1000

DECLARE @ListCountry TABLE (ListID INT, CountryID INT)

INSERT @ListCountry
SELECT 1805, 225 UNION ALL
SELECT 1805, 155 UNION ALL
SELECT 1805, 41 UNION ALL
SELECT 4302, 15 UNION ALL
SELECT 4322, 15 UNION ALL
SELECT 4322, 22 UNION ALL
SELECT 4322, 225

-- Check precondition
SELECT * FROM @Result

-- Delete unwanted records
DECLARE @ci INT

SET @ci = 225

DELETE r
FROM @Result AS r
INNER JOIN (
SELECT ListID
FROM @ListCountry
GROUP BY ListID
HAVING MAX(CASE
WHEN CountryID = @ci THEN 1
ELSE 0
END) = 0
) AS lc ON lc.ListId = r.ListId

-- Check postcondition
SELECT * FROM @Result[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:00:37

Precondition

ListID	Score
1805	1000
4302	500
4322	500
3211	1000

Postcondition

ListID	Score
1805	1000
4322	500
3211	1000

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

pOrmsby
Starting Member

12 Posts

Posted - 2007-10-09 : 09:07:52

Thanks Peso

It took just 9 minutes to get a reply that fixed it for me.

Well done.

This is the bit that I needed >>

DELETE		r
FROM		@Result AS r
INNER JOIN	(
			SELECT		ListID
			FROM		@ListCountry
			GROUP BY	ListID
			HAVING		MAX(CASE
						WHEN CountryID = @ci THEN 1
						ELSE 0
					END) = 0
		) AS lc ON lc.ListId = r.ListId

Many many thanks

Regards

Peter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:20:10

Of course. The first part is just to mimic your environment which I don't have access to.

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:24:22

Or this? This also removes the 3211 record

DELETE	r
FROM	@Result AS r
WHERE	NOT EXISTS (SELECT * FROM @ListCountry AS lc WHERE lc.CountryID = @ci AND lc.ListID = r.ListID)

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

pOrmsby
Starting Member

12 Posts

Posted - 2007-10-09 : 09:30:27

Yes, I need to also remove the 3211 record, I did not mean to exclude it from the listcountry table, it will be in there.

If the listid is in the @result table and does not appear within the listcountry table with the corresponding countryid then it should be removed.

There would your last post be more appropriate?

Peter

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-09 : 09:34:57

Yes.

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

pOrmsby
Starting Member

12 Posts

Posted - 2007-10-09 : 09:39:01

Its seems to remove all of them again?

Previous code seems ok to me.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-09 : 09:48:14

some methodology:
http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources