同表连接更新的sql

来源：互联网发布：淘宝怎么继续延长收货编辑：程序博客网时间：2024/04/30 06:19

表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

数据如下图

test

--想实现同表中将除了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'