Update without replacement?? - 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 2000 Forums

SQL Server Development (2000)

Update without replacement??

Author

Topic

inrsence
Starting Member

48 Posts

Posted - 2001-12-03 : 20:07:40

Hi,

Not sure how else to describe this, so here I go.

I've got a table that stores used ids:


CREATE TABLE #Test(ID int)
INSERT INTO #Test(1)
INSERT INTO #Test(2)
INSERT INTO #Test(3)

I've also got a master table with all available ids:


CREATE TABLE #Master(ID int)
INSERT INTO #Master(1)
INSERT INTO #Master(2)
INSERT INTO #Master(3)
INSERT INTO #Master(4)
INSERT INTO #Master(5)
INSERT INTO #Master(6)

So now the fun part! I want to create a new table that gives me new ids that aren't used yet, like so:


CREATE TABLE #NewTests (NewID int, ID int null)
INSERT INTO #NewTests(11)
INSERT INTO #NewTests(12)
INSERT INTO #NewTests(13)

And the last steps is the kicker, I don't want to re-use them so the end result of this query should be like this:

 
NewID     ID
---------------
11        4
12        5
13        6

I tried this, but end up with all null values:


UPDATE #NewTests
SET    ID = (
          SELECT Max(ID)
          FROM #Master
          WHERE ID NOT IN (SELECT ID FROM #Test) AND
                ID NOT IN (SELECT ID FROM #NewTests)
       )

It's clear that the "c" word could be used here.. but I was hoping there was something more pleasant :)

Anyone have any thoughts? Suggestions? Aspirin?

Greg

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-03 : 20:35:11

Greg,

Based on the assumption of consecutives...


declare @MinStart int
declare @MinTarget int
Select @MinStart = Min(ID) from #Master M where not exists (Select ID from #Test where ID = M.ID)
Select @MinTarget = Min(NewID) from #NewTests
update #NewTests set ID = NewID - (@MinTarget  - @MinStart)

Or all in one


update #NewTests set ID = NewID - 
	((Select Min(NewID) from #NewTests) - 
	(Select Min(ID) from #Master M where not exists (Select ID from #Test where ID = M.ID)))

The first Batch is faster!!!!!

DavidM
It gets windy at a thousand feet...."Tutorial D"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-03 : 20:37:31

Hey that was a fun one

There may be a simpler solution here, but I didn't get a good nights sleep last night.

Using the ranking technique from the last reader challenge and some derived tables I joined the values in #NewTests to the unused values in #master based on their rank.

Here is the query :



	SELECT T1.NewID, t2.id 
	FROM
	
	(
	select	n1.NewID, 
		Rank = (select count(*) + 1  
			from #NewTests n2 
			where n1.NewID > n2.NewID)
	from #NewTests n1
	
	) T1

	INNER JOIN 
	(
	select	m1.ID, 
		Rank = (select count(*) + 1  
			from #Master m2 
			where m1.ID > m2.ID and m2.id NOT IN (SELECT ID FROM #Test))
	from #Master m1
	Where m1.ID NOT IN (SELECT ID FROM #Test)
	
	) T2
	
	ON T1.Rank = T2.Rank

How does that look ?

Once that is done, it is easy to write an update statement that uses that select.



Update #NewTests

SET ID = t3.id

FROM

#NewTests

INNER JOIN (

	
	SELECT T1.NewID, t2.id 
	FROM
	
	(
	select	n1.NewID, 
		Rank = (select count(*) + 1  
			from #NewTests n2 
			where n1.NewID > n2.NewID)
	from #NewTests n1
	
	) T1

	INNER JOIN 
	(
	select	m1.ID, 
		Rank = (select count(*) + 1  
			from #Master m2 
			where m1.ID > m2.ID and m2.id NOT IN (SELECT ID FROM #Test))
	from #Master m1
	Where m1.ID NOT IN (SELECT ID FROM #Test)
	
	) T2
	
	ON T1.Rank = T2.Rank
	
	) 
	T3 ON T3.NewID = #NewTests.NewID

Give that a go.

Damian

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-03 : 20:38:34

Dammit

Like I said.......there will be a simple way.

Ignore me

Nice one David!

Damian

inrsence
Starting Member

48 Posts

Posted - 2001-12-03 : 21:34:44

Byrmol,

Excellent solution, but the ID numbers in the real table are not consecutive (sorry for being unclear) so the math used would not work properly (I'm guessing).

Merkin.. thanks a bunch (wish I had a clue what you were doing :)) but I will try and figure that out as well.

Amy thoughts on a solution for non-consecutive ids?

Thanks again for your time,
Greg

Edited by - inrsence on 12/03/2001 21:39:28

Edited by - inrsence on 12/03/2001 21:41:31

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-03 : 23:02:50

Ok here is a solution that should work every time...



while (	
Select Count(ID) from #Master M
where not exists (Select ID from #Test where ID = M.ID)
and not exists (Select ID from #NewTests where ID = M.ID)) > 0
begin
	Update #NewTests set ID  = 
	(Select Min(ID) as ID from #Master M where not exists (Select * from #Test where ID = M.ID)
		and not exists (Select * from #NewTests where ID = M.ID))
	where NewID = (Select Min(NewID) from #NewTests where ID is null)
end

or... Assuming that there will always be something to do...


Select 1
while @@RowCount > 0
begin
	Update #NewTests set ID  = 
	(Select Min(ID) as ID from #Master M where not exists (Select * from #Test where ID = M.ID)
		and not exists (Select * from #NewTests where ID = M.ID))
	where NewID = (Select Min(NewID) from #NewTests where ID is null)
end

HTH

DavidM
It gets windy at a thousand feet...."Tutorial D"

Edited by - byrmol on 12/03/2001 23:05:13

inrsence
Starting Member

48 Posts

Posted - 2001-12-04 : 11:13:44

David,

Tried your solution and understood it completely.. but it was going into an infinite loop I am pretty sure.. both techniques.. I let one run for 14 minutes :)

I eventually copped out and scripted it procedurally.. I know that sucks, but needed to get it out of the way.

Still interested in finding a solution.. I'll try and play with it some more.

Thanks for your time and effort,
greg

Sherry
Starting Member

1 Post

Posted - 2001-12-04 : 14:14:09

Access does this very nicely in a wizard called 'find unmatched'. You will end up with something like this:

SELECT master.id
FROM master LEFT JOIN test ON master.id = test.id
WHERE ((test.id) Is Null)

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-04 : 16:50:45

I am sorry about that Greg!!!

With the sample data you gave it ran well..

I think you will have to create a loop of some kind....

Good Luck...

DavidM
It gets windy at a thousand feet...."Tutorial D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources