SQL Server – How to Update with Inner Join

sql-server

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

Best Answer

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

Related Solutions

SQL Server – How to Update with Inner Join

My guess is that because you have shown us simplified schema, some info is missing that would determine why have the repeated VarietyID values for a given OrderID.

When you have multiple rows, SQL Server will arbritrarily pick one of them for the update.

If this is the case, you need to group first

UPDATE V
SET
   Stock = Stock - foo.SumQuantity
FROM
    tblVariety V
    JOIN
    (SELECT SUM(Quantity) AS SumQuantity, VarietyID
     FROM tblOrderItem
      JOIN tblOrder ON tblOrderItem.OrderId = tblOrder.OrderId  
     WHERE tblOrder.OrderId = 1
     GROUP BY VarietyID
    ) foo ON V.VarietyId = foo.VarietyId

If not, then the OrderItems table PK is wrong because if allows duplicate OrderID/VarietyID combinations (The PK should be OrderID/VarietyID, or these should be constrained unique)

Best Answer

Related Solutions

SQL Server – How to Update with Inner Join

Related Question