首页 > 代码库 > 关联更改,内部需要引用表名

关联更改,内部需要引用表名

 

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] Cleft 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] Cleft 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

关联更改,内部需要引用表名