update tb set a = (case when a > b then b else a end), b = (case when a > b then a else b end)
create table tb(A int,B int) insert into tb values(4 ,2) insert into tb values(3 ,5) insert into tb values(7 ,2) goupdate tb set a = (case when a > b then b else a end), b = (case when a > b then a else b end)select * from tbdrop table tb/* A B ----------- ----------- 2 4 3 5 2 7(所影响的行数为 3 行)*/
--------------------SQL Server数据格式化工具------------------- --------------------------------------------------------------- -- DESIGNER :happycell188(喜喜) -- QQ :584738179 -- Development Tool :Microsoft Visual C++ 6.0 C Language -- FUNCTION :CONVERT DATA TO T-SQL --------------------------------------------------------------- -- Microsoft SQL Server 2005 -- Developer Edition on Microsoft Windows XP [版本 5.1.2600] --------------------------------------------------------------- ---------------------------------------------------------------use test go if object_id('test.dbo.tb') is not null drop table tb -- 创建数据表 create table tb ( A int, B int ) go --插入测试数据 insert into tb select 4,2 union all select 3,5 union all select 7,2 go --代码实现select case when a>b then b else a end A,case when a<b then b else a end B from tb/*测试结果A B ---------- 2 4 3 5 2 7(3 行受影响) */
--要更新的话,就它了... update tb set a = (case when a > b then b else a end), b = (case when a < b then b else a end)
update tb set a = b,b = a where a>b
drop table tb go create table tb(A int,B int) insert into tb values(4 ,2) insert into tb values(3 ,5) insert into tb values(7 ,2) go update tb set B=A,A=B where A>B select * from tbA B ----------- ----------- 2 4 3 5 2 7(3 row(s) affected)
set a = (case when a > b then b else a end),
b = (case when a > b then a else b end)
insert into tb values(4 ,2)
insert into tb values(3 ,5)
insert into tb values(7 ,2)
goupdate tb
set a = (case when a > b then b else a end),
b = (case when a > b then a else b end)select * from tbdrop table tb/*
A B
----------- -----------
2 4
3 5
2 7(所影响的行数为 3 行)*/
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------use test
go
if object_id('test.dbo.tb') is not null drop table tb
-- 创建数据表
create table tb
(
A int,
B int
)
go
--插入测试数据
insert into tb select 4,2
union all select 3,5
union all select 7,2
go
--代码实现select case when a>b then b else a end A,case when a<b then b else a end B from tb/*测试结果A B
----------
2 4
3 5
2 7(3 行受影响)
*/
update tb
set a = (case when a > b then b else a end),
b = (case when a < b then b else a end)
set a = b,b = a
where a>b
drop table tb
go
create table tb(A int,B int)
insert into tb values(4 ,2)
insert into tb values(3 ,5)
insert into tb values(7 ,2)
go
update tb
set B=A,A=B
where A>B
select * from tbA B
----------- -----------
2 4
3 5
2 7(3 row(s) affected)
UP。我觉得这句比case when效率要高