Ask Your Question
0

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

Comments

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
2

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

Comments

Thank you, that worked well.

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

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 66 times

Last updated: Mar 25 '17