SQLite Forum

Updating a value with one from another table?
Login
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:

<code>
;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);
</code>

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

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

Thank you.