What is wrong with this Statement???

Databases
1 
UPDATE table1 a SET t1='True'  WHERE EXISTS(SELECT 8 FROM table2 b WHERE a.name=b.name)

SQLite… near “a”: syntax error

it doesn’t like the alias?

This seems to work

UPDATE table1 SET t1='True'  WHERE name IN (SELECT b.name FROM table2 b WHERE table1.name=b.name)

SQLite will NOT allow an alias on the table being updated :frowning:

2

Looks like you can only alias tables in the FROM section of a SELECT query.

3

SQL Server behaves the same way. I haven’t tried it in SQLite, but this is how to do it in SQL Server:

UPDATE a SET t1='True' FROM table1 a WHERE EXISTS(SELECT 8 FROM table2 b WHERE a.name=b.name)
4

Just a guess.

Have you tried:

UPDATE table1 a SET a.t1='True'  WHERE EXISTS(SELECT 8 FROM table2 b WHERE a.name=b.name)
5

SQL 2003 (the newest standard I have easy access to) does not specify an alias for the table name in 14.11 (p847) see here

As you are referring to SQLite, there is a very nice EBNF als syntax diagram in the documentation which refers to “qualified-table-name” which again does not specify an alias.

So yes, this common extension to the standard seems not to be implemented by SQLite. As we see in the examples before, DBMS that implement such an extension do vary in their syntax and meaning. In PostgreSQL for example an alias completely hides the original table name and it would have a different meaning if you specify the table to update in the FROM clause whereas SQL Server seems to encourage this.

6

[quote=338044:@Tobias Bussmann]SQL 2003 (the newest standard I have easy access to) does not specify an alias for the table name in 14.11 (p847) see here

As you are referring to SQLite, there is a very nice EBNF als syntax diagram in the documentation which refers to “qualified-table-name” which again does not specify an alias.

So yes, this common extension to the standard seems not to be implemented by SQLite. As we see in the examples before, DBMS that implement such an extension do vary in their syntax and meaning. In PostgreSQL for example an alias completely hides the original table name and it would have a different meaning if you specify the table to update in the FROM clause whereas SQL Server seems to encourage this.[/quote]
I knew I was stupid and you have just confirmed it :slight_smile:

I have re-read Dave’s original post and seen that he had already tried my suggestion anyway. Sorry for cluttering the thread.

7

I would have marked my own original post as “the answer” but it won’t let me