派生表 't' 不可更新，因为派生表中的某一列是派生的或是常量。

[海蓝树]

update t set homecode =substring(homecode,2,4) from (select homecode from dbo.Archives_CM where homecode in (select homecode from dbo.Archives_CM group by homecode having len(homecode)>4)) as t


如果要实现对于group by后的更新如何处理

[叶子]

把group by 后的数据放到临时表，然后更新临时表。

[Shawn]

UPDATE dbo.Archives_CM
SET homecode = SUBSTRING(homecode, 2, 4)
WHERE LEN(homecode) > 4

[Shawn]

或用CTE来更新.

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
*/

[cs_lb]

不知道你想做什么？

如果想更新则

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

[快溜]

--直接查，何必要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

[rucypli]

[Quote=引用 2 楼 wwwwgou 的回复:]
SQL code
UPDATE dbo.Archives_CM
SET homecode = SUBSTRING(homecode, 2, 4)
WHERE LEN(homecode) > 4
[/Quote]1

[renwenli07461]

update t set homecode =substring(homecode,2,4) from (select homecode from dbo.Archives_CM where homecode in (select homecode from dbo.Archives_CM group by homecode having len(homecode)>4) as A) as t

[--小F--]

GROUP BY后面的查询放入临时表 然后再处理

2005以上可以放入CTE再处理