views:

55

answers:

3

I'm using sql Server 2008.

following dataPool:

PK  Col1        Col2
1   SomeValue1  DataToTake1
2   SomeValue1
3   SomeValue1
4   SomeValue1
5   SomeValue2  DataToTake2
6   SomeValue2
...

i want to insert DataToTake1 into Col2 of records with PK 2, 3 and 4 and DataToTake2 into Col2 of record with PK 6.
to make it more obvious: records with PK 1-4 are groups, indicated by Col1
another info: PK might not be incremental nor sequenced on productive system (but as we can group by Col1, i hope that might not be a problem).

is there any way to to this with sql Server? (a companion implemented this with a pointer ... arggggh)

edit
thank you for your answers, but i have to revise my request, as my initial situation changed: i need to take PK in account. eg.

PK  Col1        Col2
1   SomeValue1  DataToTake1
2   SomeValue1
4   SomeValue1
5   SomeValue2  DataToTake2
6   SomeValue2
9   SomeValue1

how to only update sequenced rows? with this example, PK 1 = 2 = 4

+1  A: 
-- Select the top 1 value which has data, and matches whats in Col1
UPDATE dataPool
SET Col2 = (SELECT TOP 1 t3.Col2 
            FROM dataPool t3 
            WHERE t3.col1 = t2.col1 
            AND t3.Col2 IS NOT NULL) -- 
FROM dataPool t2
WHERE t2.Col1 = Col1
AND col2 IS NULL -- Only update Rows with NULLs in
kevchadders
thank you for solution, but my request has changed. could you maybe help?
Andreas Niedermair
+2  A: 
UPDATE
   D1
SET
   Col2 = D2.Col1
FROM
   dataPool D1
   JOIN
   dataPool D2 ON D1.col1 = D2.col1
WHERE
   D1.col2 <> D2.col2 OR D1.col2 IS NULL
gbn
thank you for solution, but my request has changed. could you maybe help?
Andreas Niedermair
This changes the code drastically. I'll see if I have time later: sorry I can't do anything now
gbn
no problem! i appreciate any help to any given time!
Andreas Niedermair
+1  A: 

I think you will have to use temporary tables:

create table #DataToTake ( seq int not null identity(1, 1), PK_S int not null, -- set this to same type as PK in dataPool PK_E int null, -- set this to same type as PK in dataPool Col1 varchar(20) null, -- set this to same type as Col1 in dataPool Col2 varchar(20) null -- set this to same type as Col2 in dataPool )

insert into #DataToTake (PK_S, Col1, Col2) select PK, Col1, Col2 from dataPool where Col2 is not null order by PK

update #DataToTake set PK_E = dtt2.PK_S from #DataToTake inner join #DataToTake dtt2 on #DataToTake.seq = dtt2.seq - 1

update #DataToTake set PK_E = (select Max(PK) + 1 from dataPool) where PK_S = (select Max(PK_S) from #DataToTake )

update dataPool set Col2 = dtt.Col2 from dataPool inner join #DataToTake dtt on dataPool.PK > dtt.PK_S and dataPool.PK < dtt.PK_E and dataPool.Col1 = dtt.Col1

drop table #DataToTake

DeanM