SQL statement for updating connections to the same table

Table 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

Data such

 

-- To update the tmpresourceid values of records with the same version and resourceid in the same table except languagecode = en-US and with the same resourceid to the value of the record with 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'