SQL update 关联更改，内部需要引用表名

 

select A.Id,A.Email,(
select C.Email from [nopCommerce].[dbo].[Customer] C 
left join [nopCommerce].[dbo].CustomerAddresses CA 
on C.Id=CA.Customer_Id where A.Id=CA.Address_Id) as 'Email2' from [nopCommerce].[dbo].[Address] A

如此有效。

 

但

update [nopCommerce].[dbo].[Address] set Email=(
select C.Email from [nopCommerce].[dbo].[Customer] C
left join [nopCommerce].[dbo].CustomerAddresses CA 
on C.Id=CA.Customer_Id where Id=CA.Address_Id) 

如此无效 数据是改了，但完全不是要的结果

update [nopCommerce].[dbo].[Address] set Email=(
select C.Email from [nopCommerce].[dbo].[Customer] C
left join [nopCommerce].[dbo].CustomerAddresses CA 
on C.Id=CA.Customer_Id where  [nopCommerce].[dbo].[Address] .Id=CA.Address_Id) 

只有在内部连接上

改为[nopCommerce].[dbo].[Address] .Id 才有效

DECLARE @a TABLE (Id INT, NewEmail NVARCHAR(200))
--INSERT INTO @a( Id, NewEmail)VALUES(1,'abc')

 

INSERT INTO @a( Id, NewEmail)
select A.Id,(
select C.Email from [nopCommerce].[dbo].[Customer] C
left join [nopCommerce].[dbo].CustomerAddresses CA
on C.Id=CA.Customer_Id where A.Id=CA.Address_Id) as 'Email2' from [nopCommerce].[dbo].[Address] A

update [nopCommerce].[dbo].[Address] set Email=(
select T.NewEmail from @a T where [nopCommerce].[dbo].[Address].Id=T.Id)

 

update [nopCommerce].[dbo].[Address] set Email=(
select C.Email from [nopCommerce].[dbo].[Customer] C
left join [nopCommerce].[dbo].CustomerAddresses CA
on C.Id=CA.Customer_Id where Id=CA.Address_Id)

select A.Id,A.Email,(
select C.Email from [nopCommerce].[dbo].[Customer] C
left join [nopCommerce].[dbo].CustomerAddresses CA
on C.Id=CA.Customer_Id where A.Id=CA.Address_Id) as 'Email2' from [nopCommerce].[dbo].[Address] A