同表连接更新的sql

最新推荐文章于 2023-08-25 17:59:04 发布

原创最新推荐文章于 2023-08-25 17:59:04 发布 · 1.9k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#null #join #table #sql

SQL相关专栏收录该内容

12 篇文章

订阅专栏

表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'