CREATE TABLE [tb_test] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[aa] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[date1] [datetime] NULL ,
CONSTRAINT [PK_tb_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('101','1011', '110', NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ee','eee' ,'eee', '2009-06-26 00:00:00.000')
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL, 'ee' ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ff ', NULL ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL ,'df' ,NULL ,NULL)我想把空值都自动 赋值
varchar 型的就用('AAA1','AAA2','AAA3',)
时间型的就为 ('1900-01-01 00:00:01','1900-01-01 00:00:02','1900-01-01 00:00:03')效果如下id aa bb cc date1
-----------------------------------------------------------------------
1 101 1011 110 1900-01-01 00:00:01.000
2 ee eee eee 2009-06-26 00:00:00.000
3 AAA1 ee AAA1 1900-01-01 00:00:02.000
4 ff AAA1 AAA2 1900-01-01 00:00:03.000
5 AAA2 df AAA3 1900-01-01 00:00:03.000
[id] [int] IDENTITY (1, 1) NOT NULL ,
[aa] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[bb] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[cc] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[date1] [datetime] NULL ,
CONSTRAINT [PK_tb_test] PRIMARY KEY CLUSTERED
(
[id]
) ON [PRIMARY]
) ON [PRIMARY]
GOINSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('101','1011', '110', NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ee','eee' ,'eee', '2009-06-26 00:00:00.000')
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL, 'ee' ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ff ', NULL ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL ,'df' ,NULL ,NULL)我想把空值都自动 赋值
varchar 型的就用('AAA1','AAA2','AAA3',)
时间型的就为 ('1900-01-01 00:00:01','1900-01-01 00:00:02','1900-01-01 00:00:03')效果如下id aa bb cc date1
-----------------------------------------------------------------------
1 101 1011 110 1900-01-01 00:00:01.000
2 ee eee eee 2009-06-26 00:00:00.000
3 AAA1 ee AAA1 1900-01-01 00:00:02.000
4 ff AAA1 AAA2 1900-01-01 00:00:03.000
5 AAA2 df AAA3 1900-01-01 00:00:03.000
set a=(case when a is null then 3 when a is not null then a end)
update ...
(select case when type='' then ....)
[id] [int] IDENTITY (1, 1) NOT NULL ,
[aa] [varchar] (50) ,
[bb] [varchar] (50) ,
[cc] [varchar] (50) ,
[date1] [datetime] )
GO INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('101','1011', '110', NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ee','eee' ,'eee', '2009-06-26 00:00:00.000')
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL, 'ee' ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ff ', NULL ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL ,'df' ,NULL ,NULL) go
declare @i int
set @I = 0
update tb_test
set aa = 'aaa'+ltrim(@i) ,@i = @i +1
where aa is nullset @I = 0
update tb_test
set bb = 'aaa'+ltrim(@i) ,@i = @i +1
where bb is nullset @I = 0
update tb_test
set cc = 'aaa'+ltrim(@i),@i = @i +1
where cc is nullset @I = 0
update tb_test
set date1 = dateadd(ss,@i,'1900-01-01 00:00:00'),@i = @i +1
where date1 is nullselect * from tb_test
id aa bb cc date1
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------------------------
1 101 1011 110 1900-01-01 00:00:01.000
2 ee eee eee 2009-06-26 00:00:00.000
3 aaa1 ee aaa1 1900-01-01 00:00:02.000
4 ff aaa1 aaa2 1900-01-01 00:00:03.000
5 aaa2 df aaa3 1900-01-01 00:00:04.000(所影响的行数为 5 行)drop table tb_test
[id] [int] IDENTITY (1, 1) NOT NULL ,
[aa] [varchar] (50) ,
[bb] [varchar] (50) ,
[cc] [varchar] (50) ,
[date1] [datetime] )
GO INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('101','1011', '110', NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ee','eee' ,'eee', '2009-06-26 00:00:00.000')
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL, 'ee' ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values('ff ', NULL ,NULL ,NULL)
INSERT INTO.[tb_test]([aa], [bb], [cc], [date1])values(NULL ,'df' ,NULL ,NULL) update t
set aa=isnull(aa, 'aaa'+cast((select count(*) from tb_test where aa is null and id<=t.id) as varchar)),
bb=isnull(bb, 'aaa'+cast((select count(*) from tb_test where bb is null and id<=t.id) as varchar)),
cc=isnull(cc, 'aaa'+cast((select count(*) from tb_test where cc is null and id<=t.id) as varchar)),
date1=isnull(date1,dateadd(ss,(select count(*) from tb_test where date1 is null and id<=t.id),'1900-01-01 00:00:00'))
from tb_test t
select * from tb_testid aa bb cc date1
1 101 1011 110 1900-01-01 00:00:01.000
2 ee eee eee 2009-06-26 00:00:00.000
3 aaa1 ee aaa1 1900-01-01 00:00:02.000
4 ff aaa1 aaa2 1900-01-01 00:00:03.000
5 aaa2 df aaa3 1900-01-01 00:00:04.000
(select case when type='' then ....)
set
aa = isnull(aa,'AAA1'),
bb = isnull(bb,'AAA2'),
cc = isnull(cc,'AAA3'),
date1 = isnull(date1,'1900-01-01 00:00:01')