同表连接更新的sql

 表com_Resource

CREATE TABLE [dbo].[COM_Resource](
 [ResourceID] [bigint] NOT NULL,
 [Version] [varchar](30) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [LanguageCode] [varchar](10) COLLATE Chinese_PRC_CI_AS NOT NULL,
 [ResourceValue] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
 [TmpResourceID] [nvarchar](max) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_COM_Resource] PRIMARY KEY CLUSTERED
(
 [ResourceID] ASC,
 [Version] ASC,
 [LanguageCode] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

数据如下图

 

 --想实现同表中将除了languageCode=en-us外的其它同version and 同resourceID的记录的TmpResourceID值都更新成和languageCode=en-us的记录的值.

 

update b set TmpResourceID=a.TmpResourceID
from com_Resource b inner join com_Resource a on b.ResourceID=a.ResourceID and b.LanguageCode='zh-cn'
where  a.LanguageCode='en-us'