Update using multiple tables - 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 2000 Forums

Transact-SQL (2000)

Update using multiple tables

Author

Topic

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-12 : 11:31:57

Hi,

I have two tables both with similar fields. They both store customer records whereby a customer_id field uniquely identifies each customer. There are address fields in both tables. What I want to do is to update field "new_street" in tablea to equal field "new_street" in tableb where the customer id's in both tables equal each other.
The problem is I don't know how to reference another table from within an update query.

Any Ideas?

Cheers

Paul

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 11:43:31

Here you go:

CREATE TABLE Tablea (id int, NewStreet varchar(25) )
Go
CREATE TABLE Tableb (id int, NewStreet varchar(25))
Go

Insert Into Tablea (id, NewStreet) Values (1,'Old Street 1')
Insert Into Tableb (id, NewStreet) Values (1,'New Street 1')
Insert Into Tablea (id, NewStreet) Values (2,'Old Street 2')
Insert Into Tableb (id, NewStreet) Values (2,'New Street 2')
Go

Select * from Tablea a, Tableb b Where a.id = b.id
Go

UPDATE a
SET a.NewStreet = b.NewStreet
FROM Tablea a, Tableb b
WHERE a.Id = b.Id
Go

Select * from Tablea a, Tableb b Where a.id = b.id
Go

DROP TABLE Tablea
Go
DROP TABLE Tableb
Go

Brett

8-)

paul.rowling
Yak Posting Veteran

81 Posts

Posted - 2003-03-12 : 13:33:34

Thanks Brett, worked a treat!!!

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-12 : 13:59:34

Worked a "Treat". I have a brother in-law in London..never heard that one before

Brett

8-)

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-03-12 : 16:10:49

quote:

UPDATE a
SET a.NewStreet = b.NewStreet
FROM Tablea a, Tableb b
WHERE a.Id = b.Id

Select * from Tablea a, Tableb b Where a.id = b.id

Just curious, Brett, but I wonder why you did not use the ANSI-92 syntax for the joins (Tablea INNER JOIN Tableb).

OS

X002548
Not Just a Number

15586 Posts

Posted - 2003-03-13 : 08:54:26

Just being lazy I guess. I'm not sure if there is a difference. SHOW PLAN doesn't show any. I'm sure the optimizer breaks it down most efficiently anyway.

I usually use the JOIN syntax, esp. when things start to get complicated.

So the answer is pure laziness.

"A good DBA is a lazy DBA"

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources