首页 > 代码库 > 关联更改,内部需要引用表名
关联更改,内部需要引用表名
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
关联更改,内部需要引用表名