Update with Subquery

Question

Hello there,

i have a Problem with a query.

I have a huge Table with Zipcodes from Germany called 'Postleitzahlen'. There is another table with Companies called 'Firmen'

Structure is like this:

Firmen
------
ID
City
State
ZipCode


Postleitzahlen
--------------
ZipCode
State

Now I want, all empty ( '' or NULL ) State-fields of Firmen updated with their correspendants of Postleitzahlen

That's my actual query:

UPDATE 
    Firmen
SET 
    Firmen.State = Postleitzahlen.State 
FROM
    Postleitzahlen
INNER JOIN 
    Firmen ON Postleitzahlen.ZipCode = Firmen.ZipCode 
WHERE 
    (
      Firmen.State = ''
   OR Firmen.State IS NULL )

I get a response with xx affected rows, but in fact, there's nothing updated.

Can someone help me?

Thanks in advance.

• dennis

Answer 1

That looks like it should work correctly. What I would do is run the below query:

SELECT Firmen.State,Postleitzahlen.State 
FROM
    Postleitzahlen
INNER JOIN 
    Firmen ON Postleitzahlen.ZipCode = Firmen.ZipCode 
WHERE 
    (
      Firmen.State = ''
   OR Firmen.State IS NULL )

See what that gets you. If your get results with values in both columns then you probably have a different issue. However, I am guessing that one of the columns is null or empty since you are updating rows but nothing is changed.

Then there has to be nothing wrong with your query. I think it might be with your update then. Try making the table an alias, like this:

UPDATE 
    F
SET 
    F.State = Postleitzahlen.State 
FROM
    Postleitzahlen
INNER JOIN 
    Firmen F ON Postleitzahlen.ZipCode = F.ZipCode 
WHERE 
    (
      F.State = ''
   OR F.State IS NULL )

Answer 2

UPDATE 
    Firmen f
SET 
    f.State = (SELECT p.State FROM Postleitzahlen p WHERE p.ZipCode = f.ZipCode)
WHERE 
    (
      f.State = ''
   OR f.State IS NULL )