求一Update语句.

[Juchiyufei]

两个表 t_val, t_val2，以id为键, 将 t_val2中的val与 t_val中的val比较，如果相等result 为true, 不相等为false

表： t_val
id val
----------- --------------------------------------------------
1 A
2 B
3 AC
4 C
5 AD

(5 row(s) affected)

表： t_val2
id val result
----------- ------------------------------- --------------------------------------------------
1 A NULL
2 A NULL
3 AB NULL
4 D NULL
5 AD NULL

CREATE TABLE [dbo].[t_val](
	[id] [int] NOT NULL,
	[val] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_t_val] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[t_val2](
	[id] [int] NOT NULL,
	[val] [nvarchar](50) NOT NULL,
	[result] [nvarchar](50) NULL,
 CONSTRAINT [PK_t_val2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into t_val(id, val) values(1, 'A') 
insert into t_val(id, val) values(2, 'B') 
insert into t_val(id, val) values(3, 'AC')
insert into t_val(id, val) values(4, 'C') 
insert into t_val(id, val) values(5, 'AD')

insert into t_val2(id, val) values(1, 'A') 
insert into t_val2(id, val) values(2, 'A') 
insert into t_val2(id, val) values(3, 'AB')
insert into t_val2(id, val) values(4, 'D') 
insert into t_val2(id, val) values(5, 'AD')

-- 用它我可以计算出来，但我不知道怎么去更新result。
select a.id, result=(case b.val when a.val then 'true' else 'false' end from t_val a left join t_val2 b on a.id=b.id

update t_val2  set result=t.result  
	from (select a.id, result=(case b.val when a.val then 'true' else 'false' end) from t_val a left join t_val2 b on a.id=b.id) t
where id=t.id

[秋风雾晨]

UPDATE 
	t_val2
SET
	result = CASE WHEN t_val2.val = t_val.val THEN 'true' ELSE 'false' END
FROM
	t_val
WHERE
	t_val2.id = t_val.id

[Juchiyufei]

引用 1 楼 jackilin 的回复:

UPDATE 
	t_val2
SET
	result = CASE WHEN t_val2.val = t_val.val THEN 'true' ELSE 'false' END
FROM
	t_val
WHERE
	t_val2.id = t_val.id

CASE 与 WHEN 之间不要一个啥的。

[發糞塗牆]

没问题啊

CREATE TABLE [dbo].[t_val](
	[id] [int] NOT NULL,
	[val] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_t_val] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[t_val2](
	[id] [int] NOT NULL,
	[val] [nvarchar](50) NOT NULL,
	[result] [nvarchar](50) NULL,
 CONSTRAINT [PK_t_val2] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

insert into t_val(id, val) values(1, 'A') 
insert into t_val(id, val) values(2, 'B') 
insert into t_val(id, val) values(3, 'AC')
insert into t_val(id, val) values(4, 'C') 
insert into t_val(id, val) values(5, 'AD')

insert into t_val2(id, val) values(1, 'A') 
insert into t_val2(id, val) values(2, 'A') 
insert into t_val2(id, val) values(3, 'AB')
insert into t_val2(id, val) values(4, 'D') 
insert into t_val2(id, val) values(5, 'AD')


SELECT * FROM t_val2

UPDATE  b
SET b.result=CASE WHEN a.val=b.val THEN 'TRUE' ELSE 'FALSE' END 
FROM t_val2 b INNER JOIN t_val a ON b.id=a.id

SELECT * FROM t_val2

/*
id          val                                                result
----------- -------------------------------------------------- --------------------------------------------------
1           A                                                  NULL
2           A                                                  NULL
3           AB                                                 NULL
4           D                                                  NULL
5           AD                                                 NULL

(5 行受影响)

(5 行受影响)

id          val                                                result
----------- -------------------------------------------------- --------------------------------------------------
1           A                                                  TRUE
2           A                                                  FALSE
3           AB                                                 FALSE
4           D                                                  FALSE
5           AD                                                 TRUE

*/