update tb set a2 = case when a1 is not null and a2 is null then 0 else a2 end
update tb set a2='0' from tb where a1 is not null and a2 is null
UPDATE table_name SET a2 = '0' WHERE ISNULL(a1, '') <> '' AND ISNULL(a2, '') = ''
DECLARE @table TABLE (a1 INT,a2 int) insert INTO @table SELECT 1,NULL UNION ALL SELECT 2,3 UNION ALL SELECT NULL,5 UNION ALL SELECT NULL,NULL--不做处理的查询结果 SELECT * FROM @table /* a1 a2 ----------- ----------- 1 NULL 2 3 NULL 5 NULL NULL */--空处理为0 SELECT ISNULL(a1,0) AS a1,ISNULL(a2,0) AS a2 FROM @table /* a1 a2 ----------- ----------- 1 0 2 3 0 5 0 0 */ --第一个不是null 第二个做处理 SELECT a1,ISNULL(a2,0) AS a2 FROM @table WHERE a1 IS NOT NULL UNION ALL SELECT a1,a2 FROM @table WHERE a1 IS NULL /* a1 a2 ----------- ----------- 1 0 2 3 NULL 5 NULL NULL */
create table tb ( a1 varchar(100), a2 varchar(100) ) insert into tb select null,null union all select null,'1' union all select 'aa',null union all select 'aa','bb'update tb set a2='0' from tb where a1 is not null and a2 is nullselect * from tb /* a1 a2 NULL NULL NULL 1 aa 0 aa bb */
原来我错了(第三次),请参考如下SQL: CREATE TABLE #temp ( a1 TEXT, a2 NTEXT )INSERT INTO #temp SELECT 'a', '' UNION ALL SELECT 'b', NULL UNION ALL SELECT 'c', 'test'UPDATE #temp SET a2 = '0' WHERE (a1 IS NOT NULL AND DATALENGTH(a1) > 0) AND (a2 IS NULL OR DATALENGTH(a2) = 0)
update table1 a,(select * from table1) b set a.a2=0 where a.id=b.id and b.a1 is not null and b.a2 is null;
update tb set a2=case when a1 is not null then 0 else a2 end where a2 is null
create table te(id int,ar varchar(10),br varchar(10)) insert into te select 1,null,null union all select 2,'a',null union all select 3,null,'b' union all select 4,'b',null goupdate te set br = case when ar is not null and br is null then '0' else br endselect * from tedrop table teid ar br ----------- ---------- ---------- 1 NULL NULL 2 a 0 3 NULL b 4 b 0(4 行受影响)
update tb
set a2 = case when a1 is not null and a2 is null then 0 else a2 end
set a2='0'
from tb where a1 is not null and a2 is null
SET a2 = '0'
WHERE ISNULL(a1, '') <> '' AND ISNULL(a2, '') = ''
DECLARE @table TABLE (a1 INT,a2 int)
insert INTO @table
SELECT 1,NULL UNION ALL
SELECT 2,3 UNION ALL
SELECT NULL,5 UNION ALL
SELECT NULL,NULL--不做处理的查询结果
SELECT * FROM @table
/*
a1 a2
----------- -----------
1 NULL
2 3
NULL 5
NULL NULL
*/--空处理为0
SELECT ISNULL(a1,0) AS a1,ISNULL(a2,0) AS a2 FROM @table
/*
a1 a2
----------- -----------
1 0
2 3
0 5
0 0
*/
--第一个不是null 第二个做处理
SELECT a1,ISNULL(a2,0) AS a2 FROM @table WHERE a1 IS NOT NULL
UNION ALL
SELECT a1,a2 FROM @table WHERE a1 IS NULL
/*
a1 a2
----------- -----------
1 0
2 3
NULL 5
NULL NULL
*/
(
a1 varchar(100),
a2 varchar(100)
)
insert into tb
select null,null union all
select null,'1' union all
select 'aa',null union all
select 'aa','bb'update tb
set a2='0'
from tb where a1 is not null and a2 is nullselect * from tb
/*
a1 a2
NULL NULL
NULL 1
aa 0
aa bb
*/
CREATE TABLE #temp
(
a1 TEXT,
a2 NTEXT
)INSERT INTO #temp
SELECT 'a', '' UNION ALL
SELECT 'b', NULL UNION ALL
SELECT 'c', 'test'UPDATE #temp
SET a2 = '0'
WHERE (a1 IS NOT NULL AND DATALENGTH(a1) > 0) AND (a2 IS NULL OR DATALENGTH(a2) = 0)
set a.a2=0
where a.id=b.id and b.a1 is not null and b.a2 is null;
create table te(id int,ar varchar(10),br varchar(10))
insert into te
select 1,null,null union all
select 2,'a',null union all
select 3,null,'b' union all
select 4,'b',null
goupdate te
set br = case when ar is not null and br is null then '0' else br endselect * from tedrop table teid ar br
----------- ---------- ----------
1 NULL NULL
2 a 0
3 NULL b
4 b 0(4 行受影响)