CODEWITHSUNDEEP
sqlsql-server-2008
nsilva
nsilva

Reputation: 5612

SQL - setting two different values in one line of SQL

I have the following SQL which does exactly as intended:

UPDATE Zones 
SET Zones.Zone = (SELECT Zone FROM Zones_New WHERE Zones_New.ZONENO = ZONES.ZONENO)

However, this updates table Zones and sets the field Zone to be what the Zone name is in table Zones_New and matches this by the ZoneNo.

At the same time I want to do exactly the same but also set the Name field to what the Zone name is in table Zones_New.

So basically I'm setting Zone & Name to be what the value Zone is from table Zones_New

I just can't figure out the SQL so I would appreciate any help :)

Upvotes: 2

Views: 290

Answers (4)

TechDo
TechDo

Reputation: 18649

UPDATE Zones
SET Zones.Zone = Zones_New.Zone,
    Zones.Name = Zones_New.Name
FROM Zones, Zones_New
WHERE Zones.ZONENO = Zones_New.ZONENO

Upvotes: 0

lc.
lc.

Reputation: 116498

UPDATE Zones
SET Zones.Zone = Zones_New.Zone,
    Zones.Name = Zones_New.Name
FROM Zones
INNER JOIN Zones_New ON Zones.ZONENO = Zones_New.ZONENO

Note if you want to purposely set nulls for non-matching rows, you can use a LEFT JOIN instead.

Upvotes: 3

freefaller
freefaller

Reputation: 19953

The simplest would be just...

UPDATE Zones 
SET Zones.Zone = (SELECT Zone FROM Zones_New WHERE Zones_New.ZONENO = ZONES.ZONENO),
    Zones.Name = (SELECT Zone FROM Zones_New WHERE Zones_New.ZONENO = ZONES.ZONENO)

A more complex could be something like...

UPDATE Z
SET Zones.Zone = ZN.Zone
FROM Zones Z
  LEFT JOIN Zones_New ZN
    ON Z.ZONENO = ZN.ZONENO

Upvotes: 0

SWeko
SWeko

Reputation: 30902

You could just run the subquery twice like this:

UPDATE Zones 
 SET Zones.Zone = (SELECT Zone 
                   FROM Zones_New 
                   WHERE Zones_New.ZONENO = ZONES.ZONENO),
     Zones.Name = (SELECT Zone 
                   FROM Zones_New 
                   WHERE Zones_New.ZONENO = ZONES.ZONENO)

or you could do it in a join 

UPDATE Zones 
 SET Zones.Zone = Zones_New.Zone
     Zones.Name = Zones_New.Zone
FROM Zones
  LEFT OUTER JOIN Zones_New 
    ON Zones_New.ZONENO = ZONES.ZONENO

Both of this queries will null the data if a corresponding record is not found in the Zones_New table.

Upvotes: 0

Related Questions