We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

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

asked 2017-03-24 21:21:23 +0200

Quarto Die gravatar image

updated 2017-03-24 21:22:29 +0200

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.

edit retag flag offensive close merge delete


Thank you for the help!

Quarto Die gravatar imageQuarto Die ( 2017-04-01 14:57:20 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-03-24 23:46:37 +0200

Ratslinger gravatar image

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' )
edit flag offensive delete link more


Thank you, that worked well.

Quarto Die gravatar imageQuarto Die ( 2017-04-01 14:57:56 +0200 )edit

answered 2017-03-24 23:39:28 +0200

EasyTrieve gravatar image

updated 2017-03-25 00:22:03 +0200

[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'        ) 
edit flag offensive delete link more

Question Tools

1 follower


Asked: 2017-03-24 21:21:23 +0200

Seen: 123 times

Last updated: Mar 25 '17