SQLite Forum

INNER JOIN error
Login

INNER JOIN error

(1) By jz1280 on 2020-09-11 11:32:20 [link]

Hi

I am a SQLite newbie. Can anyone please tell me why the following query results in the reported error

sqlite> UPDATE [activity_overview] INNER JOIN [query_projects] ON [activity_overview].[Project ID] = [query_projects].[Project ID] SET [activity_overview].[C3] = 1 WHERE [query_projects].[Planned Start]) > [activity_overview].[Early Start]
   ...> ;
Error: near "INNER": syntax error
sqlite>

The same query works on other SQL systems such as MS Access.

Thanks in anticipation of your help

JZ

(2) By Larry Brasfield (LarryBrasfield) on 2020-09-11 13:40:05 in reply to 1 [link]

That a given SQL-like construct "works" on other database systems which accept something like SQL does not mean you should expect SQLite to accept it.

To see what you can reasonably expect, consult [SQL As Understood By SQLite](https://sqlite.org/lang.html).  Further, lose the square-bracket identifier delimiters.  In SQL, double-quote characters are used for that purpose.

(3) By Richard Hipp (drh) on 2020-09-11 15:03:25 in reply to 1 [link]

I have never heard of UPDATE INNER JOIN before, and I suspect that is
an extension that is unique to MS Access.

The [latest release][1] of SQLite adds support for [UPDATE FROM][2].
This is also a non-standard SQL extension, but in the case of SQLite,
we attempted to copy the syntax of [PostgreSQL][3] as closely as we
could.  I suspect that you will want to translate your query into
a suitable UPDATE FROM statement.

[1]: https://www.sqlite.org/releaselog/3_33_0.html
[2]: https://www.sqlite.org/lang_update.html#upfrom
[3]: https://www.postgresql.org/docs/9.1/sql-update.html

The offical way to quote identifiers in SQL is double quotes:

~~~~
     UPDATE "activity_overview" ....
~~~~

The use of `[...]` for identifier quoting is distinctive to SQL Server and
MS-Access.  SQLite tries to copy that for compatibility.  But you really
should get into the habit of using the SQL standard `"..."` quoting if you can.

(4) By jz1280 on 2020-09-11 16:37:29 in reply to 3 [link]

Hi Richard (and Larry)

Many thanks for your responses. 

Your comments on square brackets are noted. I am much more familiar with Access/SQL Server and when the convention worked in SQLite I just stuck with it. I am currently exploring the use of UPDATE FROM and am hopeful that it will give me the result I need. If I get stuck I will come back to the forum

atb

JZ

(5) By anonymous on 2020-09-11 16:57:34 in reply to 3

The SQL standard is to use double quotes for wrapping names that are keywords used as names or contain spaces.

SQLite uses " and ' interchangeably (almost).

The alternate use of square brackets for wrapping keywords used as names or names that contain spaces. The ODBC driver I am using, namely, http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe does also allow square brackets BUT the CLI doesn't.

Received wisdom also suggests that SQL keywords be capitalised. Neither the CLI nor the ODBC driver enforce this.

My session:

sqlite> drop table if exists tbl;
sqlite> create temp table tbl (myName);
sqlite> insert into tbl values("myName"); -- double quotes for literal
sqlite> select "myName" from tbl; -- double quotes for column name
myName
------
myName
sqlite> select "myname"; -- double quotes for literal
"myname"
--------
myname

sqlite> /* Alternatively  ... less ambiguity? */

sqlite> select "myname"; -- double quotes for literal ... RETURNS quotes in column name
"myname"
--------
myname
sqlite> select [myName] from tbl; -- square brackets for column name
myName
------
myName
sqlite> select 'myname'; -- double quotes for literal -- NOW single quotes uotes in column name
'myname'
--------
myname


I note your point about the SQL standard but 
- SQLite is also using single quotes to wrap column names (not the SQL standard)
- the use of square brackets does enhance readability, I think.

PS. Should .echo on ignote /* block comments */

(6) By Larry Brasfield (LarryBrasfield) on 2020-09-11 17:21:32 in reply to 5 [link]

> The SQL standard is to use double quotes for wrapping names that are keywords used as names or contain spaces.

The SQL standard allows double-quoted identifiers, whether or not they would otherwise be treated as something else.

> SQLite uses " and ' interchangeably (almost).

No, it does not. (The "almost" almost saves that assertion, but it misleads enough to make it a useless qualifier.)

What SQLite does, (and can be built to not do, fortunately), is allow SQL with incorrect quoting to be parsed "successfully" [a] in certain circumstances. There is little point in elaborating them; they amount to tolerating the wrongly quoted token when it saves the parse. It is much better, particularly for novices, to just use single-quote for string literals and double-quote for identifiers rather than trying to exploit that tolerance. Doing it right takes less brain power, develops better habits, and is less likely to produce surprise or disappointment.

[a. This would be for some but not all definitions of "success". ]

(7) By anonymous on 2020-09-11 17:51:55 in reply to 6 [link]

>No, it does not

sqlite> select [City] AS SBracket,"City" AS DQuotes,'CitySQuotes',"CityDQuotes"   from employees limit 1;

SBracket  DQuotes   'CitySQuotes'  "CityDQuotes"
--------  --------  -------------  -------------
Edmonton  Edmonton  CitySQuotes    CityDQuotes

... the names of the last two columns.

Correction: The CLI DOES allow square brackets; the Dot Commands do NOT.

(8) By Keith Medcalf (kmedcalf) on 2020-09-11 21:58:47 in reply to 1 [link]

Your syntax is incorrect.

```
UPDATE activity_overview 
   SET C3 = 1 
  FROM query_projects 
 WHERE activity_overview."Project ID" = query_projects."Project ID"
   AND query_projects."Planned Start" > activity_overview."Early Start"
;
```

Notes:

 - double-quote are used to quote ill-conceived identifiers, not square brackets.  Although SQLite3 will recognize square brackets, that is NOT the standard.  You do not need to use identifier-quotes except where the identifier is ill-conceived although you are free to do so (just as you are free to write SQL while taking a bath or while standing on your head) even though doing so (excessive useless identifier-quoting and/or using ill-conceived identifiers) makes the statement more difficult for humans to parse.  Some things (like anything from Microsoft) loves to sprinkle turds of extraneous parenthesis and quoting where it is not required just because spreading turds is **The Microsoft Way**.

 - for each activity_overview there must only be one applicable query projects record (the WHERE clause must specify a 1:1 relationship).  If this is not the case then this update is ill-conceived and may not work as you expect.  If in fact there may be more than one query_projects record per activity overview then you need to fix the query so that it is not nonsense, perhaps by converting the FROM and WHERE constraints into an EXISTS correlate to avoid that issue.  (In this particular case you might not have this problem although the query itself may be ill-conceived and ill-designed).

(9) By jz1280 on 2020-09-12 11:36:01 in reply to 8 [link]

Hi and thanks to everyone for the interest.

Keith, I tried you suggestion which ran without  a problem from the CLI but throws an error when run from SQLite Studio and also when I tried to run it from the Python interface. Also the output when run from the CLI was suspect, possibly for one of the reasons you described.

In the mean time I have followed Larry's advise and gone back to the documentation. My latest attempt at this query is:

sqlite> UPDATE activity_overview
   ...> SET 'D3' = 1
   ...> FROM activity_overview INNER JOIN query_projects ON activity_overview."Project ID"= query_projects."Project ID"
   ...> WHERE activity_overview."Early Start" < query_projects."Planned Start";
Error: ambiguous column name: activity_overview.Early Start
sqlite>

Which gets further but unfortunately still throws the above error. I have followed the flow chart documented in 'SQL As Understood By SQLite - Update' but I am obviously still missing something.

At the risk of testing your patience, can anyone give me a further pointer?

Atb 

JZ

(10) By Simon Slavin (slavin) on 2020-09-12 13:03:02 in reply to 9 [link]

Here's your command:

<code>UPDATE activity_overview
    SET 'D3' = 1
    FROM activity_overview
        INNER JOIN query_projects
        ON activity_overview."Project ID"= query_projects."Project ID"
        WHERE activity_overview."Early Start" < query_projects."Planned Start"</code>

Here's the error message:

<code>Error: ambiguous column name: activity_overview.Early Start</code>

So look at your SQLite command and search for things that might define <code>activity_overview.Early Start</code>.

And there are indeed two of them.  You use the table name <code>activity_overview</code> twice.  Once immediately after the <code>UPDATE</code> keyword, and again after <code>FROM</code>.

When you refer to <code>activity_overview</code> SQLite doesn't know whether to use the current row of the <code>UPDATE</code> command or the current row from the <code>INNER JOIN</code>.

One way to fix this would be to use a table alias for one of them.  For instance

<code>UPDATE activity_overview AS activity_update …</code>

(11) By Simon Slavin (slavin) on 2020-09-12 13:07:27 in reply to 10 [link]

Also you are using single quotes for <code>D3</code>.  You should not be quoting column names.  They should go unquoted.

Single quotes and double quotes are used for completely different purposes in SQLite.  The fact that you can sometimes get the 'right' result is a nasty historical artefact which only confuses people.

(13) By Richard Damon (RichardDamon) on 2020-09-12 13:38:06 in reply to 11 [link]

It would be perfectly fine to enclose D3 in DOUBLE quotes "D3", Things in double quotes will always be seen as an identifier (like table or column name) assuming that is a valid by the context, even if the contents of the double quote would otherwise not qualify.

You could defensively double quote ALL such identifiers in a statement and never get an issue. 

The only case where they could give you a problem if you used them in a context where an identifier was not allowed, but a string was.

(12) By TripeHound on 2020-09-12 13:09:58 in reply to 9 [link]

Whenever you use a third-party tool/wrapper (such as SQLite Studio or some (unspecified) Python wrapper), be aware that they will (almost always) not be using your CLI instance of SQLite.

[The docs](https://www.sqlite.org/lang_update.html#update_from) state that the `UPDATE ... FROM` variant was only introduced in version 3.33.0 of SQLite, so run:

```sql
select sqlite_version() ;
```

from within SQLite Studio and/or through Python to see what version of SQLite _they_ are using.