主页 / coding / 问题 / 76921019
Accepted
Surender Raja
Asked: 2023-08-17 19:20:35 +0800 CST

TSQL 中的循环和更新语句不起作用

  • 772

如何拆分分隔字符串 TSQL 并通过循环（while 或 for）并根据拆分值更新表列

基本上 dbo.omega 需要根据 @source 的值更新latest_version 列

declare @source [varchar](500)
begin 
set @source = "employee:0,dept:1" # it is comma delimited string 

 #  I want to write logic for these two updates in a single update stament by looping through @source 
 #UPDATE dbo.omega set latest_version = 0 where obj_name = 'employee'
 #UPDATE dbo.omega set latest_version = 1 where obj_name = 'dept'

 splitarr = @source.split(",")
 i = 0
 while(i < len(splitarr)):
  UPDATE dbo.omega set latest_version = splitarr(i)
  i =i+1 
 end
sql-server

1 个回答

  1. Best Answer

    您可以使用STRING_SPLIT拆分字符串，然后使用CHARINDEX和SUBSTRINGinAPPLY来获得两半，最后将所有内容连接起来omega并更新。

    DECLARE @source varchar(max) = 'employee:0,dept:1';

UPDATE o
SET latest_version = v2.value
FROM STRING_SPLIT(@source, ',') s
CROSS APPLY (VALUES(
    NULLIF(CHARINDEX(':', s.value), 0)
)) v1(colon)
CROSS APPLY (VALUES(
    SUBSTRING(s.value, 1, v1.colon - 1),
    TRY_CAST(SUBSTRING(s.value, v1.colon + 1, LEN(s.value)) AS int)
)) v2(name, value)
JOIN omega o ON o.obj_name = v2.name
WHERE s.value IS NOT NULL;

    如果您只使用表值参数或表变量，那就更好了

    DECLARE @source TABLE (name varchar(50) PRIMARY KEY, value int NOT NULL);
INSERT @source VALUES
('employee', 0),
('dept', 1);

UPDATE o
SET latest_version = s.value
FROM @source s
JOIN omega o ON o.obj_name = s.name;
    • 2

相关问题