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