Can one via sql update one or a group of records in one table from another?

[QuartoDie]

Currently I am using a split-database. When I was using an embedded database such sql commands as the following worked for me:

UPDATE “addresses”
SET “AREACODE” = (SELECT “AREACODE” FROM “tnumbers” WHERE “addresses”.“ID” = “tnumbers”.“ID” AND “tnumbers”.“city” = ‘OMAHA’ )

Now when I run this, in effect, all of the records with the city of OMAHA are in fact updated in the “AREACODE” field, but in all other records the existing “AREACODE” data is completely deleted. I have tried several other ways of getting around this bu seem to always come up with the same result.

[QuartoDie]

Thank you for the help!

[Ratslinger]

Your update statement does not specify which of the records in the “addresses” table to update so records not in the select statement are updating with nothing.

This statement will update only records in “addresses” table which have a corresponding record in “tnumbers” table. This means that if there is a record in the “addresses” table with a “city” = ‘OMAHA’ but there is NOT a corresponding record in the “tnumbers” table, it will not change.

UPDATE "addresses" SET "AREACODE" = (SELECT "AREACODE" FROM "tnumbers" WHERE "addresses"."ID" = "tnumbers"."ID" AND "tnumbers"."city" = 'OMAHA' ) where EXISTS (SELECT "AREACODE" FROM "tnumbers" WHERE "addresses"."ID" = "tnumbers"."ID" AND "tnumbers"."city" = 'OMAHA' )

[QuartoDie]

Thank you, that worked well.

[EasyTrieve]

[Edit: I think Ratslinger’s answer is better.

Also I forgot an important paren in my original answer here. Fixed below.

My only tips at this point, is to please pretty print your SQL in some way like this, so it’s easier for everyone to QUICKLY see what’s going on.]

UPDATE "addresses" 
    SET "AREACODE" = (
        SELECT ifnull("AREACODE",'missing area code') 
        FROM "tnumbers" 
        WHERE (
           (ifnull("addresses"."ID" ,0) = "tnumbers"."ID") AND 
           (ifnull("tnumbers"."city",0) = 'OMAHA'        ) 
    )
)