Sqlsever条件更新、替换

需要修改一部分数据
参考数据如下：

img

我需要把每个sht的pcsindex 1，3对应的resultcode对调，变成：

img

我尝试过
1.case when than
2.游标存取
的思路，但最终还是欠缺，没能完整的实现

我想要达到的结果

希望能帮我解答下如何才能实现

你都用游标了，再借助个临时表行不？先select id,(select resultcode from t t2 where t2.sht=t1.sht and ((t1.pcsindex=1 and t2.pcsindex=3) or (t1.pcsindex=3 and t2.pcsindex=1)) as RC from t t1

举个示例：将id=6的name字段值改为wokou
【向表中“替换插入”一条数据，如果原表中没有id=6这条数据就作为新数据插入(相当于insert into作用)；如果原表中有id=6这条数据就做替换(相当于update作用)。对于没有指定的字段以默认值插入】

replace into test_tb VALUES(6,‘wokou’,‘新九州岛’,‘日本’)

如果上面的插入替换方式不OK，
下面这几种不同的替换插入的方式供你参考：
普通插入（全字段）：INSERT INTO table_name VALUES (value1, value2, …)
普通插入（限定字段）：INSERT INTO table_name (column1, column2, …) VALUES (value1, value2, …)
多条一次性插入：INSERT INTO table_name (column1, column2, …) VALUES (value1_1, value1_2, …), (value2_1, value2_2, …), …
从另一个表导入：INSERT INTO table_name SELECT * FROM table_name2 [WHERE key=value]
带更新的插入：REPLACE INTO table_name VALUES (value1, value2, …) （注意这种原理是检测到主键或唯一性索引键重复就删除原记录后重新插入）

这个简单，不用游标也不用case when啥的：

img

DECLARE @a TABLE(id INT,sht CHAR(6),pcsindex INT,pcs CHAR(4),resultcode INT)
INSERT @a SELECT 1,'000000',1,'aaaa',1200
UNION ALL SELECT 2,'000000',2,'bbbb',0
UNION ALL SELECT 3,'000000',3,'cccc',0
UNION ALL SELECT 4,'000000',4,'dddd',0
UNION ALL SELECT 5,'000000',5,'eeee',0
UNION ALL SELECT 6,'000000',6,'ffff',0
UNION ALL SELECT 7,'111111',1,'qqqq',0
UNION ALL SELECT 8,'111111',2,'pppp',0
UNION ALL SELECT 9,'111111',3,'zzzz',1500
UNION ALL SELECT 10,'111111',4,'llll',0
UNION ALL SELECT 11,'111111',5,'mmmm',0
UNION ALL SELECT 12,'111111',6,'hhhh',0

UPDATE a SET resultcode = b.resultcode    
FROM  @a a INNER JOIN 
(SELECT *
FROM @a 
WHERE pcsindex IN (1,3) ) b 
ON a.sht=b.sht AND a.pcsindex <>b.pcsindex
WHERE a.pcsindex IN(1,3)


SELECT * FROM @a

img

-- 拆分为两个sql更新语句

update td set resultcode=(select top 1 resultcode from tb a where a.sht=b.sht and pcsindex=3 ) where pcsindex=1;

update td set resultcode=(select top 1 resultcode from tb a where a.sht=b.sht and pcsindex=1) where pcsindex=3;

hello world老哥说的是完全正确的，满分。

借助个临时表

结合问题回答下的思路，终于是有了一个方法：
/create table map1(
i bigint identity NOT NULL,
r int null,
n int null
)/ --没有此表则新建

declare @lotno varchar(11)
set @lotno='11111111111'--lot搞里头
declare @shtbarcode varchar(40)
declare sht_Cursor CURSOR for select distinct ShtBarcode from pcsresults where LotNo=@lotNo and ShtBarcode in(
'000001',
'000002')--sht条码搞里头

open sht_Cursor
FETCH NEXT FROM sht_Cursor into @shtbarcode

while @@fetch_status=0
begin
DELETE map1
print 'shtbarcode='+ @shtbarcode
insert into map1(r,n)
SELECT
ResultCode,CASE
WHEN PcsIndex=25 then 48
WHEN PcsIndex=26 then 47
WHEN PcsIndex=27 then 46
WHEN PcsIndex=28 then 45
WHEN PcsIndex=29 then 44
WHEN PcsIndex=30 then 43
WHEN PcsIndex=31 then 42
WHEN PcsIndex=32 then 41
WHEN PcsIndex=33 then 40
WHEN PcsIndex=34 then 39
WHEN PcsIndex=35 then 38
WHEN PcsIndex=36 then 37
WHEN PcsIndex=37 then 36
WHEN PcsIndex=38 then 35
WHEN PcsIndex=39 then 34
WHEN PcsIndex=40 then 33
WHEN PcsIndex=41 then 32
WHEN PcsIndex=42 then 31
WHEN PcsIndex=43 then 30
WHEN PcsIndex=44 then 29
WHEN PcsIndex=45 then 28
WHEN PcsIndex=46 then 27
WHEN PcsIndex=47 then 26
WHEN PcsIndex=48 then 25
end --数据与实际白油对应关系

FROM pcsresults
WHERE shtbarcode=@shtbarcode and LotNo =@lotno and RouteId =29 AND PcsIndex BETWEEN 25 AND 48

--只对调实际与电测白油对应的resultscod
UPDATE r
SET ResultCode=m1.r

FROM pcsresults r
    INNER JOIN map1 m1   ON r.PcsIndex =m1.n
WHERE shtbarcode =@shtbarcode AND  LotNo=@lotno and RouteId =29 AND PcsIndex BETWEEN 25 AND 48

FETCH NEXT FROM sht_Cursor into @shtbarcode

end

print '关闭与释放游标'
--关闭与释放游标
CLOSE sht_Cursor
DEALLOCATE sht_Cursor