Inserting ID field from separate table
-
I had a friend ask how to populate a null ID value in a target table with a value from a separate unique ID table. (My first question was why were the ids null in the first table)
The code below works. I was looking for something a bit more elegant.
create table table1
(id int ,
myvalue varchar(20)
)
create table id_table
(id2 int)
insert into table1 (myvalue)
select 'first choice' union
select 'second choice' union
select 'third choice'
insert into id_table
select 2 union
select 8 union
select 4
SELECT number = IDENTITY(int, 1, 1), myvalue myvalue2
INTO #n1
from table1
SELECT number2 = IDENTITY(int, 1, 1), id2
INTO #n2
from id_table
update table1
set id = id2
from (select myvalue2, id2 from #n1, #n2 where number = number2) mydata
where myvalue = myvalue2
More an intellectual question then actual problem.
thanks
Daryl
-
Hi Daryl. Interesting problem - thanks for posting it
Off the top of my head, here's one possibility...
--data
create table #table1
(id int ,
myvalue varchar(20)
)
create table #id_table
(id2 int)
insert into #table1 (myvalue)
select 'first choice' union all --note union all works better
select 'second choice' union all
select 'third choice' union all
select 'fourth choice'
insert into #id_table
select 2 union all
select 8 union all
select 4 union all
select 9
--calculation
update t set id = id2 from #table1 t,
(select a.myvalue, count(*) as id from #table1 a, #table1 b where a.myvalue >= b.myvalue group by a.myvalue) c,
(select a.id2, count(*) as id from #id_table a, #id_table b where a.id2 >= b.id2 group by a.id2) d
where t.myvalue = c.myvalue and c.id = d.id
--results
select * from #table1
--tidy up
drop table #table1
drop table #id_table
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
And here's a slightly "crazier" option...
--data
create table #table1
(id int ,
myvalue varchar(20)
)
create table #id_table
(id2 int)
insert into #table1 (myvalue)
select 'first choice' union all
select 'second choice' union all
select 'third choice' union all
select 'fourth choice'
insert into #id_table
select 2 union all
select 8 union all
select 4 union all
select 9
--calculation
select number = identity(int, 1, 1), * into #n1 from #table1, #id_table
update a set id = id2 from #table1 a inner join #n1 b on a.myvalue = b.myvalue
where number % (select count(*) + 1 from #table1) = 1
--results
select * from #table1
--tidy up
drop table #table1
drop table #id_table
drop table #n1
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
-
Thanks Ryan Randall,
The trick select
(select a.myvalue, count(*) as id from #table1 a, #table1 b where a.myvalue >= b.myvalue group by a.myvalue) c,
was the statement I was looking for. I had seen the self-connect before and had forgotten the details. Very Slick.
thanks again
Daryl Smith
(stolen engineers toast)
We the willing , led by the unknowing,
Have done so much , for so long , with so little,
We are now qualified to do anything with nothing
-
No worries Daryl. Always fun to play with intellectual questions
I was particularly proud of the confusing nature of one of the statements in the "crazier option"... "where number % (select count(*) + 1 from #table1) = 1"
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply