在两行数据之间交换值
下面的T-SQL代码段可以工作,但是我想知道是否有一个更聪明和/或不太详细的方式来完成两个不同行之间的字段值交换。 (该代码已经硬编码PKEY值的简单性。)在两行数据之间交换值
BEGIN TRAN;
declare @swapFormSeqA int;
declare @swapFormSeqB int;
SELECT @swapFormSeqA = DisplaySeq
FROM CustomCatalogForm
WHERE CustomCatalogFormId = 1;
SELECT @swapFormSeqB = DisplaySeq
FROM CustomCatalogForm
WHERE CustomCatalogFormId = 2;
UPDATE CustomCatalogForm
SET DisplaySeq = @swapFormSeqB
WHERE CustomCatalogFormId = 1;
UPDATE CustomCatalogForm
SET DisplaySeq = @swapFormSeqA
WHERE CustomCatalogFormId = 2;
COMMIT TRAN;
编辑:我使用Sql2k5特别如此2K8不是一个选项。
你可以这样做:
begin tran
declare @sum int
select @sum = sum(DisplaySeq)
from CustomCatalogForm
where CustomCatalogFormId in (1,2)
update CustomCatalogForm
set DisplaySeq = @sum - DisplaySeq
where CustomCatalogFormId in (1,2)
commit tran
聪明的把戏;只要添加的DisplaySeq值不会超出该类型的限制即可使用。 – 2009-08-25 22:42:05
非常有趣的技巧Guffa,但我没有看到它是确定性的?例如。你如何保证这些值在Update调用中实际交换?有一些堆叠逻辑是你所依赖的吗? – 2009-08-26 12:05:34
@psasik:其实很简单。首先得到(x + y),然后你可以用它来计算另一个值:x =(x + y)-y和y =(x + y)-x。 – Guffa 2009-08-26 12:18:49
像这样:
UPDATE CustomCatalogForm
SET DisplaySeq = (SELECT DisplaySeq
FROM CustomCatalogForm T2
WHERE T2.CustomCatalogFormId =
-1*(CustomCatalogForm.CustomCatalogFormId -2) +1
)
WHERE CustomCatalogFormId IN (1,2);
(警告:请测试一下,第一,因为我不能从这里测试它)。
你还没有在任何地方定义过T1,并且你没有在任何地方使用T2 ... – Guffa 2009-08-25 22:00:32
Yup,hnxs。应该是T2两个地方。 – RBarryYoung 2009-08-25 22:25:17
假设你的表是这样的:
--drop table CustomCatalogForm
create table CustomCatalogForm
(
CustomCatalogFormId int not null
,DisplaySeq char(1) not null
)
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
values (1,'A')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
values (2,'B')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
values (3,'C')
insert CustomCatalogForm (CustomCatalogFormId, DisplaySeq)
values (4,'D')
那么这将做到这一点对于任何两个给定值(I 2测试3):
select * from CustomCatalogForm
-------------------------------
DECLARE
@Item1 int
,@Item2 int
SET @Item1 = 2
SET @Item2 = 3
UPDATE CustomCatalogForm
set DisplaySeq = ccf2.DisplaySeq
from CustomCatalogForm ccf
inner join (select CustomCatalogFormId, DisplaySeq
from CustomCatalogForm
where CustomCatalogFormId = @Item1
or CustomCatalogFormId = @Item2) ccf2
on ccf.CustomCatalogFormId <> ccf2.CustomCatalogFormId
where ccf.CustomCatalogFormId = @Item1
or ccf.CustomCatalogFormId = @Item2
-------------------------------
select * from CustomCatalogForm
诀窍是让这两个行仅在连接的任一侧,并且在NOT EQUALS上连接。
试试这个:
UPDATE ccf SET
DisplaySeq = Case CustomCatalogFormId
When 1 Then T2.DisplaySeq
When 2 Then T1.DisplaySeq End
From CustomCatalogForm ccf
Join CustomCatalogForm T1 On T1.CustomCatalogFormId = 1
Join CustomCatalogForm T2 On T2.CustomCatalogFormId = 2
Where ccf.CustomCatalogFormId In (1,2)
SQL 2000,2005年或2008年?在MERGE和OUTPUT周围有2k8个有趣的技巧,请参阅http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying -and-love-the-merge.aspx – 2009-08-25 21:47:14