Update with inner join?

Question

I have two databases:

DB1 and DB2

How to do something like:

update myServer.DB1.dbo.hotels.Name = myServer.DB2.dbo.hotels.Name

join myServer.DB2.dbo.hotels on myServer.DB2.dbo.hotels.Code=  myServer.DB1.dbo.hotels.Code
where myServer.DB2.dbo.hotels.CountryCoe !=  myServer.DB1.dbo.hotels.CountryCode

score 11 · Accepted Answer · answered May 17 '10 at 13:51

11

UPDATE  h1
SET     h1.name = h2.name
FROM    myServer.DB1.dbo.hotels h1
JOIN    myServer.DB2.dbo.hotels h2
ON      h2.Code = h1.Code
WHERE   h1.CountryCode <> h2.CountryCode

answered May 17 '10 at 13:51

Quassnoi

413,100
91
616
614

Picked this over SQLMenace' identicial version because ON clause is on it's own line -- necessary, when you have AND clauses within your ONs... – Philip Kelley May 17 '10 at 14:13

score 2 · Answer 2 · answered May 17 '10 at 13:52

2

example

update h1 
set h1.Name = h2.Name
from DB1.dbo.hotels.Name h1
join DB2.dbo.hotels  h2 on h1.Code= h2.Code
where h2.CountryCoe !=  h1.CountryCode

answered May 17 '10 at 13:52

SQLMenace

132,095
25
206
225

score 1 · Answer 3 · edited Sep 26 '11 at 17:03

1

This should do the trick for you. You were missing the SET and FROM clauses.

UPDATE DB1
SET DB1.Name = DB2.Name 
FROM DB1.dbo.hotels DB1
INNER JOIN DB2.dbo.hotels DB2 
ON DB2.Code = DB1.Code 
WHERE DB2.CountryCoe !=  DB1.CountryCode

edited Sep 26 '11 at 17:03

Adriano Carneiro

57,693
12
90
123

answered May 17 '10 at 13:56

Anthony Martin

11
1

Update with inner join?

3 Answers3

Linked