有两表,想把TB2的code UPDATE到 TB1,但在size1及size2中有NULL值。
TB1
code name size1 size2
NULL 螺丝 12厘1M 条
NULL 螺丝 12厘 NULL
NULL 螺丝 NULL NULL
NULL 螺丝 NULL NULL
NULL 螺丝 12厘1M 条TB2
code name size1 size2
BX220 螺丝 12厘1M 条
BX221 螺丝 12厘 NULL
BX222 螺丝 NULL NULL想把TB1 UPDATE成:
TB1
code name size1 size2
BX220 螺丝 12厘1M 条
BX221 螺丝 12厘 NULL
BX222 螺丝 NULL NULL
BX222 螺丝 NULL NULL
BX220 螺丝 12厘1M 条但用UPDATE TB1 SET TB1.CODE=TB2.CODE FROM TB2 where TB2.name=TB1.name and TB2.size1=TB1.size1 and TB2.size2=TB1.size2 不成,请教!
TB1
code name size1 size2
NULL 螺丝 12厘1M 条
NULL 螺丝 12厘 NULL
NULL 螺丝 NULL NULL
NULL 螺丝 NULL NULL
NULL 螺丝 12厘1M 条TB2
code name size1 size2
BX220 螺丝 12厘1M 条
BX221 螺丝 12厘 NULL
BX222 螺丝 NULL NULL想把TB1 UPDATE成:
TB1
code name size1 size2
BX220 螺丝 12厘1M 条
BX221 螺丝 12厘 NULL
BX222 螺丝 NULL NULL
BX222 螺丝 NULL NULL
BX220 螺丝 12厘1M 条但用UPDATE TB1 SET TB1.CODE=TB2.CODE FROM TB2 where TB2.name=TB1.name and TB2.size1=TB1.size1 and TB2.size2=TB1.size2 不成,请教!
UPDATE TB1
SET TB1.CODE=TB2.CODE
FROM
T1,TB2 where TB2.name=TB1.name and TB2.size1=TB1.size1 and TB2.size2=TB1.size2
UPDATE TB1
SET TB1.CODE=TB2.CODE
FROM
TB1,TB2 where TB2.name=TB1.name and TB2.size1=TB1.size1 and TB2.size2=TB1.size2
如果用UPDATE TB1 SET TB1.CODE=TB2.CODE FROM TB2 where TB2.name=TB1.name
TB1的CODE可全部更换(但不准)如果用UPDATE TB1 SET TB1.CODE=TB2.CODE FROM TB2 where TB2.name=TB1.name and TB2.size1=TB1.size1
TB1的CODE部分更换
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'TB1')
BEGIN
DROP TABLE TB1
END
GO
CREATE TABLE TB1
( code VARCHAR(10),
name VARCHAR(10),
size1 VARCHAR(10),
size2 VARCHAR(10)
)
INSERT INTO TB1
SELECT NULL, '螺丝', '12厘','1M 条' UNION ALL
SELECT NULL, '螺丝', '12厘', NULL UNION ALL
SELECT NULL, '螺丝', NULL, NULL UNION ALL
SELECT NULL, '螺丝', NULL, NULL UNION ALL
SELECT NULL, '螺丝', '12厘','1M 条'SELECT * FROM TB1IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'TB2')
BEGIN
DROP TABLE TB2
END
GO
CREATE TABLE TB2
( code VARCHAR(10),
name VARCHAR(10),
size1 VARCHAR(10),
size2 VARCHAR(10)
)
INSERT INTO TB2
SELECT 'BX220', '螺丝', '12厘','1M 条' UNION
SELECT 'BX221', '螺丝', '12厘', NULL UNION
SELECT 'BX222', '螺丝', NULL, NULL
UPDATE TB1 SET code = B.Code FROM TB2 AS B WHERE TB1.name = B.name AND CASE WHEN TB1.size1 IS NULL THEN '1'
ELSE TB1.size1 END = CASE WHEN B.size1 IS NULL THEN '1'
ELSE B.size1 END
AND CASE WHEN TB1.size2 IS NULL THEN '1'
ELSE TB1.size2 END = CASE WHEN B.size2 IS NULL THEN '1'
ELSE B.size2 END
SELECT * FROM TB1
go
create table [TB1]([code] varchar(5),[name] varchar(4),[size1] varchar(6),[size2] varchar(2))
insert [TB1]
select null,'螺丝','12厘1M','条' union all
select null,'螺丝','12厘',null union all
select null,'螺丝',null,null union all
select null,'螺丝',null,null union all
select null,'螺丝','12厘1M','条'
go
if object_id('[TB2]') is not null drop table [TB2]
go
create table [TB2]([code] varchar(5),[name] varchar(4),[size1] varchar(6),[size2] varchar(2))
insert [TB2]
select 'BX220','螺丝','12厘1M','条' union all
select 'BX221','螺丝','12厘',null union all
select 'BX222','螺丝',null,null
goUPDATE TB1 SET TB1.CODE=TB2.CODE FROM TB2
where TB2.name=TB1.name
and (TB2.size1=TB1.size1 or tb1.size1 is null and tb2.size1 is null)
and (TB2.size2=TB1.size2 or tb1.size2 is null and tb2.size2 is null)select * from tb1
/**
code name size1 size2
----- ---- ------ -----
BX220 螺丝 12厘1M 条
BX221 螺丝 12厘 NULL
BX222 螺丝 NULL NULL
BX222 螺丝 NULL NULL
BX220 螺丝 12厘1M 条(5 行受影响)
**/