自联表的数据更新问题

[hyl8218]

数据:
Email ListId Name Status FormatType PlanId BatchFlag
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------- ------ ---------- ----------- ---------
[e-mail] 2 姓名 1 0 0 0
[e-mail] 7 姓名 1 0 0 0
[e-mail] 8 姓名 1 1 0 0
[e-mail] 9 姓名 1 0 0 0
[e-mail] 2 0 1 1 0
[e-mail] 7 0 1 1 0
[e-mail] 8 0 1 1 0


语句如下
UPDATE [Subscribes]
SET [Status] = [OldSubscribes].[Status]
,[FormatType] = [OldSubscribes].[FormatType]
,[PlanId] = [OldSubscribes].[PlanId]
,[BatchFlag] = [OldSubscribes].[BatchFlag]
FROM [Subscribes] AS [OldSubscribes]
WHERE [OldSubscribes].[Email]='[e-mail]' AND [Subscribes].[Email]='[e-mail]'
AND [OldSubscribes].[ListId]=[Subscribes].[ListId]

错误:
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "Subscribes.Email"。
消息 4104，级别 16，状态 1，第 1 行
无法绑定由多个部分组成的标识符 "Subscribes.ListId"。

[dawugui]

针对你的数据,你需要什么结果,把结果贴出来,最好说明一下你的要求.

[hyde100]

这个试试

UPDATE [Subscribes] 
  SET [Status] = [OldSubscribes].[Status] 
      ,[FormatType] = [OldSubscribes].[FormatType] 
      ,[PlanId] = [OldSubscribes].[PlanId] 
      ,[BatchFlag] = [OldSubscribes].[BatchFlag] 
FROM [Subscribes] 
	INNER JOIN [Subscribes] [OldSubscribes] ON 条件...
WHERE [OldSubscribes].[Email]='[e-mail]' AND [Subscribes].[Email]='[e-mail]' 
AND [OldSubscribes].[ListId]=[Subscribes].[ListId]

[dawugui]

看你的意思是想改这六条数据中的某些值?

Email ListId Name Status FormatType PlanId BatchFlag
[e-mail] 2 姓名 1 0 0 0
[e-mail] 7 姓名 1 0 0 0
[e-mail] 8 姓名 1 1 0 0
[e-mail] 2 0 1 1 0
[e-mail] 7 0 1 1 0
[e-mail] 8 0 1 1 0

[中国风]

update b
 SET [Status] = a.[Status]
      ,[FormatType] = a.[FormatType]
      ,[PlanId] = a.[PlanId]
      ,[BatchFlag] = a.[BatchFlag]
from 
	[Subscribes] a
join
	[Subscribes] b on a.[Email]='[e-mail]' AND b.[Email]='[e-mail]'AND a.[ListId]=b.[ListId]

[中国风]

use tempdb
go
--> --> 
 
if not object_id('Subscribes') is null
	drop table Subscribes
Go
Create table Subscribes([Email] nvarchar(16),[ListId] int,[Name] nvarchar(2),[Status] int,[FormatType] int,[PlanId] int,[BatchFlag] int)
Insert Subscribes
select N'[e-mail]',2,N'姓名',1,0,0,0 union all
select N'[e-mail]',7,N'姓名',1,0,0,0 union all
select N'[e-mail]',8,N'姓名',1,1,0,0 union all
select N'[e-mail]',9,N'姓名',1,0,0,0 union all
select N'[e-mail]',2,N'0',1,1,0,null union all
select N'[e-mail]',7,N'0',1,1,0,null union all
select N'[e-mail]',8,N'0',1,1,0,null
Go
update b
 SET [Status] = a.[Status]
      ,[FormatType] = a.[FormatType]
      ,[PlanId] = a.[PlanId]
      ,[BatchFlag] = a.[BatchFlag]
from 
    [Subscribes] a
join
    [Subscribes] b on a.[Email]='[e-mail]' AND b.[Email]='[e-mail]'AND a.[ListId]=b.[ListId]
go
Select * from Subscribes


Email            ListId      Name Status      FormatType  PlanId      BatchFlag
---------------- ----------- ---- ----------- ----------- ----------- -----------
[e-mail]  2           姓名   1           0           0           0
[e-mail]  7           姓名   1           0           0           0
[e-mail]  8           姓名   1           1           0           0
[e-mail]  9           姓名   1           0           0           0
[e-mail] 2           0    1           0           0           0
[e-mail] 7           0    1           0           0           0
[e-mail] 8           0    1           1           0           0

(7 個資料列受到影響)

[terrybogero]

UPDATE t1
SET [Status] = t2.[Status]
,[FormatType] = t2.[FormatType]
,[PlanId] = t2.PlanId]
,[BatchFlag] = t2.[BatchFlag]
FROM [Subscribes] t1 inner join [Subscribes] t2 on t1.[ListId]=t2.[ListId]
WHERE t2.[Email]='[e-mail]' AND t1.[Email]='[e-mail]'

[hyl8218]

期望的结果是
数据:
Email ListId Name Status FormatType PlanId BatchFlag
-------------------------------------------------------------------------------------------------------------------------------- ----------- -------- ------ ---------- ----------- ---------
[e-mail] 2 姓名 1 0 0 0
[e-mail] 7 姓名 1 0 0 0
[e-mail] 8 姓名 1 1 0 0
[e-mail] 9 姓名 1 0 0 0
[e-mail] 2 1 0 0 0
[e-mail] 7 1 0 0 0
[e-mail] 8 1 1 0 0