求个UPDATE语句.效率好1点的.

Question

col_a   col_b
        aaa
        bbb
        ccc
        ...UPDATE后,实现以下效果
col_a   col_b
1        aaa
2       bbb
3       ccc
N        ...

Answer 1

declare @i int
set @i=0
update tb set col_a=@i,@i=@i+1

Answer 2

with t as(select *,row_id=row_number() over(order by getdate()) from tb)
update col_a=row_id

Answer 3

update
  tb 
set
  col_a=a.col_a
from
  (select col_a=row_number()over(order by getdate()),* from tb)a,
  tb b
where
  a.col_b=b.col_b

Answer 4

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2010-04-26 11:40:58
-- Verstion:
--      Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) 
-- May 26 2009 14:24:20 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([col_a] sql_variant,[col_b] varchar(3))
insert [tb]
select null,'aaa' union all
select null,'bbb' union all
select null,'ccc'
--------------开始查询--------------------------
update
  tb 
set
  col_a=a.col_a
from
  (select col_a=row_number()over(order by getdate()),col_b from tb)a,
  tb b
where
  a.col_b=b.col_b
 
select * from tb
----------------结果----------------------------
/* col_a                                                                                                                                                                                                                                                            col_b
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----
1                                                                                                                                                                                                                                                                aaa
2                                                                                                                                                                                                                                                                bbb
3                                                                                                                                                                                                                                                                ccc(3 行受影响)
*/

Answer 5

IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(COL1 VARCHAR(20),COL2 VARCHAR(20))
INSERT INTO TB(COL1)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
DECLARE @I INT
SET @I=0
UPDATE TB SET COL2=COL1,COL1=@I,@I=@I+1SELECT * FROM TB
/*
1 A
2 B
3 C
*/

Answer 6

update tb set col_a = (select count(1) from tb where col_b < t.col_b) from tb t