UPDATE __table_metadata
SET list_table_id = NULL
WHERE list_table_id NOT IN (select distinct id
from __tables_metadata);
You are correct though, aliases of the table being updated are normally only
assigned in the FROM clause, not in the UPDATE clause, though many vendors have
implemented proprietary extenstions which permit the assignment of table alias
names in the UPDATE clause.
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of Domingo Alvarez Duarte
> Sent: Thursday, 14 April, 2016 06:43
> To: SQLite mailing list
> Subject: [sqlite] Table alias not accepted with "delete/update" ?
>
> Hello !
>
> Today I'm trying to execute this statememt and it works:
>
> SELECT * FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
> sqlite_master where tbl_name=a.name);
>
> Then I tried this one and it fails:
>
> DELETE FROM __tables_metadata a WHERE NOT EXISTS(SELECT 1 FROM
> sqlite_master
> where tbl_name=a.name);
>
> Then I changed it to and it works:
>
> DELETE FROM __tables_metadata WHERE NOT EXISTS(SELECT 1 FROM sqlite_master
> where tbl_name=__tables_metadata.name);
>
> Then I needed this and it doesn't work (here I'm referring the same table
> twice):
>
> UPDATE __tables_metadata a set list_table_id=NULL where NOT EXISTS(SELECT
> 1
> FROM __tables_metadata WHERE id=a.list_table_id);
>
>
>
> It seems to be a bug/missing implementation ?
>
> Cheers !
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
