Complicated update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Complicated update

Author

Topic

kwacz23
Starting Member

44 Posts

Posted - 2013-09-13 : 09:42:37

Hi

I would like to add fake address for all customer which whey do not have ( I would like to use one query). This was excercise I got.

this is query what helping me basing is from Adevnture works 2008
select *
from [Sales].[Customer] c
join [Person].[Person] p on c.[PersonID] = p.[BusinessEntityID]
join Person.BusinessEntity e on p.BusinessEntityID = e.BusinessEntityID
join Person.BusinessEntityAddress bea on e.BusinessEntityID = bea.BusinessEntityID
join person.Address a on a.AddressID = bea.AddressID

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-13 : 10:00:20

[code]UPDATE a
SET a.xxx = 'yyy'
FROM Person.Address AS a
INNER JOIN Person.BusinessEntityAddress AS bea ON bea.AddressID = a.AddressID
INNER JOIN Person.BusinessEntity AS e ON e.BusinessEntityID = bea.BusinessEntityID
INNER JOIN Person.Person AS p ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN Sales.Customer AS c ON c.[PersonID] = p.[BusinessEntityID];[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-13 : 12:04:45

You might need

WHERE a.xxx IS NULL

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources