Help updating table using multiple joins

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

General SQL Server Forums

New to SQL Server Programming

Help updating table using multiple joins

Author

Topic

rleash
Starting Member

3 Posts

Posted - 2011-07-26 : 13:00:55

This seems pretty basic, but I'm still not very familiar with SQL syntax.

I currently have two tables:
T1: Clients (including ClientID, AddressID,...) and
T2: Addresses (including AddressID, Street, City, Zip,...).

I want to update T2 with the addresses I have in a third table:
T3: New_Add (ClientID, Street, City, Zip,...)

My guess is something like this...?

UPDATE T2
SET T2.Street = T3.Street,
T2.City = T3.City,
T2.Zip = T3.Zip
FROM Addresses T2
INNER JOIN Clients T1
ON T1.AddressID = T2.AddressID
INNER JOIN New_Add T3
ON T1.ClientID = T3.ClientID

Will this update every address and only every address in T3 (New_Add)?

TIA!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 13:06:30

it should be like


DECLARE @INSERTED_ADD table
(
AddressID int,
Street varchar(100),
City varchar(100),
Zip varchar(100)..
)

INSERT INTO Addresses (Street,City,Zip..)
OUTPUT AddressID,Street,City,Zip.. INTO @INSERTED_ADD
SELECT Street,City,Zip,...
FROM New_Add

INSERT INTO Clients (ClientID,AddressID,..)
SELECT na.ClientID,a.AddressID,...
FROM New_Add na
JOIN @INSERTED_ADD a
ON a.Street = na.Street
AND a.City=na.City
AND a.Zip = na.Zip...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rleash
Starting Member

3 Posts

Posted - 2011-07-26 : 13:12:05

I'm not sure if you understood correctly. I'm not inserting any information, only updating. The tables of clients and addresses already exist, I am only updating their addresses with corrected values.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-26 : 13:16:17

ok.then your update looks fine. it updates only addresses which are present in t3

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rleash
Starting Member

3 Posts

Posted - 2011-07-26 : 13:33:09

Thank you!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources