SQLite Forum

Updating a value with one from another table?
Login

Updating a value with one from another table?

(1) By Gilles on 2021-03-18 20:31:50 [link] [source]

Hello,

I've done some reading and experimenting, but I can't figure out how to update a value from a value found in other table:

;Error: ambiguous column name: zip.CODE_STAT UPDATE t1 SET ZIPCODE=t2.ZIPCODE FROM t1,t2 WHERE t2.CODE_STAT=t1.CODE_STAT;

;BAD : uses value of first row for all rows UPDATE t1 SET ZIPCODE=(SELECT t2.ZIPCODE FROM t1,t2 WHERE t2.CODE_STAT = t1.CODE_STAT);

Should I use rowids somehow, and possibly a second SELECT?

FWIW, I'm using SQLite 3.35.2 from 2021-03-17.

Thank you.

(2) By Larry Brasfield (larrybr) on 2021-03-18 20:52:57 in reply to 1 [link] [source]

create table PenPals(name text, street text, zip text); insert into PenPals(name) values ('Fran'),('George'); create table KnownZips(name text, zip text); insert into KnownZips(name,zip) values ('George','12345'),('Fran','67890'); update PenPals as pp set zip=(select kz.zip from KnownZips kz where kz.name=pp.name); .header on select name, zip from PenPals; name|zip Fran|67890 George|12345

(5) By Gilles on 2021-03-18 21:15:50 in reply to 2 [link] [source]

update PenPals as pp set zip=(select kz.zip from KnownZips kz where kz.name=pp.name);

Thank you very much, and to Keith as well.

(3) By Keith Medcalf (kmedcalf) on 2021-03-18 21:09:02 in reply to 1 [link] [source]

The table t1 is already included in the set of tables and does not need to be listed twice (unless you want to use it twice). This is different from some other SQL versions in which UPDATE <table> SET ... FROM <table> WHERE ... the <name> after the UPDATE is merely describing which table in the join is to be updated and the syntax form is merely an implementation of UPDATE ... WHERE CURRENT OF CURSOR.

See https://sqlite.org/lang_update.html in particular ss 2.2

You probably mean:

update t1
   set zipcode = t2.zipcode
  from t2
 where t2.code_stat = t1.code_stat
;

or as a correlated query:

update t1
   set zipcode = (
                  select zipcode
                    from t2
                   where t2.code_stat = t1.code_stat
                 )
 where exists (
               select *
                 from t2
                where t2.code_stat = t1.code_stat
              )
;

both of the above being identical in effect -- the first one being shorter to type and resulting in a better plan.

(4) By Keith Medcalf (kmedcalf) on 2021-03-18 21:13:51 in reply to 3 [source]

Also providing the same result would be:

update t1
   set zipcode = (
                  select zipcode
                    from t2
                   where t2.code_stat = t1.code_stat
                 )
 where code_stat in (
                     select code_stat 
                       from t2
                    )
;

Again, the first form UPDATE ... FROM ... will likely result in the most efficient plan.

(6) By Gilles on 2021-03-18 22:33:54 in reply to 4 [link] [source]

Both queries work, but the first one is a lot slower:

UPDATE t1 SET zip=(SELECT t2.ZIP FROM t2 WHERE t2.CODE_STAT=t1.CODE_STAT);

UPDATE t1 set zip=null;

UPDATE t1 SET zip=t2.zip FROM t2 WHERE t2.CODE_STAT=t1.CODE_STAT;