Question

I m 使用了2008年ql服务器。

缩略语集合:

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).

是否有办法用 s服务器来做到这一点? (一个伙伴用一位协调人......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

如何更新顺序排列的浏览量? PK 1 = 2 = 4

Answer 1

我认为,你必须使用临时表格:

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

表2

Answer 2

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

Answer 3

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

友情链接