關聯更改，內部需要參考資料表名

標籤：style   blog   color   ar   資料   div   sp   log   on   

 

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

關聯更改，內部需要參考資料表名