22,199
社区成员
发帖
与我相关
我的任务
分享
--直接查,何必要update,
select substring(homecode,2,4) as homecode
from dbo.Archives_CM
where homecode in (select homecode from dbo.Archives_CM group by homecode having len(homecode)>4)
--或者
select homecode into #tb
from dbo.Archives_CM
where homecode in (select homecode from dbo.Archives_CM group by homecode having len(homecode)>4)
update t set homecode =substring(homecode,2,4)
from #tb t
UPDATE dbo.Archives_CM
SET homecode = SUBSTRING(homecode, 2, 4)
WHERE LEN(homecode) > 4
SELECT SUBSTRING(homecode, 2, 4) AS homecode
FROM dbo.Archives_CM
WHERE LEN(homecode) > 4
CREATE TABLE #Archives_CM(homecode VARCHAR(10))
INSERT #Archives_CM
SELECT '1234' UNION ALL
SELECT '12345' UNION ALL
SELECT '123456' UNION ALL
SELECT '123456'
GO
--SQL:
;WITH cte AS
(
SELECT homecode
FROM #Archives_CM
WHERE homecode IN ( SELECT homecode
FROM #Archives_CM
GROUP BY homecode
HAVING LEN(homecode) > 4 )
)
UPDATE t
SET homecode = SUBSTRING(homecode, 2, 4)
FROM cte t
GO
--RESULT
SELECT * FROM #Archives_CM
/*
1234
2345
2345
2345
*/
UPDATE dbo.Archives_CM
SET homecode = SUBSTRING(homecode, 2, 4)
WHERE LEN(homecode) > 4