How to convert SELECT w/ subselect to UPDATE?

[kellyterryjones]

How do I convert this query (which works fine) into an UPDATE statement?:

SELECT geonameid,
 (SELECT geonameid FROM geonames WHERE
  (admin1_code = gn1.admin1_code AND country_code = gn1.country_code
  AND adm = -1)
 ) AS parent FROM geonames gn1 WHERE adm = -2 AND parent IS NOT NULL;

I want to set geonames.adm to the value of parent above. My attempt:

UPDATE geonames gn1 SET adm =
 (SELECT geonameid FROM geonames WHERE
  (admin1_code = gn1.admin1_code AND country_code = gn1.country_code
  AND adm = -1)
 ) AS parent WHERE adm = -2 AND parent IS NOT NULL;

fails with 'SQL error: near "gn1": syntax error'.

--
We're just a Bunch Of Regular Guys, a collective group that's trying
to understand and assimilate technology. We feel that resistance to
new ideas and technology is unwise and ultimately futile.
_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[Igor Tandetnik]

Kelly Jones <kelly.terry.jones@...>
wrote:

> How do I convert this query (which works fine) into an UPDATE
> statement?:
>
> SELECT geonameid,
> (SELECT geonameid FROM geonames WHERE
>  (admin1_code = gn1.admin1_code AND country_code = gn1.country_code
>  AND adm = -1)
> ) AS parent FROM geonames gn1 WHERE adm = -2 AND parent IS NOT NULL;
>
> I want to set geonames.adm to the value of parent above. My attempt:
>
> UPDATE geonames gn1 SET adm =
> (SELECT geonameid FROM geonames WHERE
>  (admin1_code = gn1.admin1_code AND country_code = gn1.country_code
>  AND adm = -1)
> ) AS parent WHERE adm = -2 AND parent IS NOT NULL;
>
> fails with 'SQL error: near "gn1": syntax error'.

You can't give alias to the table mentioned in UPDATE, but you can instead alias one in the subselect. Something like this:

UPDATE geonames SET adm = coalesce(
 (SELECT gn2.geonameid FROM geonames gn2
  WHERE geonames.admin1_code = gn2.admin1_code AND
    geonames.country_code = gn2.country_code AND gn2.adm = -1
 ), adm)
WHERE adm = -2;

Igor Tandetnik

_______________________________________________
sqlite-users mailing list
sqlite-users@...
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users