How to use SQL alias within query

Question

I have a sql query (below) to insert records into a database. The goal is to insert only new / unique entries in the database. So it will insert the row if the row doesn't already exist in the entire database. I would like to have it evaluate that row against ONLY the rows that have the symbol in the instrumentSymbol, rather than evaluating against every row in the entire database.

I am running into this syntax error which I have pinpointed to be due to the use of an alias in line SELECT 1 FROM instrumentsHistory WHERE instrumentSymbol = 'ZYME' AS f

This query works fine if removing the WHERE instrumentSymbol = {SYMBOL} conditional, but I need to this to refine the set of records the query compares itself to (thus reducing time to complete task.

I have looked through the documentation which leads me to believe there is nothing wrong with this query. Can someone pls point me in the right direction?

The Error:

(sqlite3.OperationalError) near "AS": syntax error
[SQL: INSERT INTO instrumentsHistory (datetime, instrumentSymbol, observation, observationColName)
                    SELECT t.datetime, t.instrumentSymbol, t.observation, t.observationColName
                    FROM tempTable t
                    WHERE NOT EXISTS 
                        (SELECT 1 FROM instrumentsHistory WHERE instrumentSymbol = 'ZYME' AS f
                         WHERE t.datetime = f.datetime
                         AND t.instrumentSymbol = f.instrumentSymbol
                         AND t.observation = f.observation
                         AND t.observationColName = f.observationColName)]

EDIT 1: Adding complete query...

 sql = f"""INSERT INTO instrumentsHistory (datetime, instrumentSymbol, observation, observationColName)
                SELECT t.datetime, t.instrumentSymbol, t.observation, t.observationColName
                FROM tempTable t
                WHERE NOT EXISTS 
                    (SELECT 1 FROM instrumentsHistory WHERE instrumentSymbol = '{symbol}' AS f
                     WHERE t.datetime = f.datetime
                     AND t.instrumentSymbol = f.instrumentSymbol
                     AND t.observation = f.observation
                     AND t.observationColName = f.observationColName)"""

Replace AS f WHERE substring in your query text with AND substring. — Akina
– Akina, Commented May 20, 2020 at 11:20
That wouldn't work because the query is referncing the table 'f afterwards. For example WHERE i.datetime = f.datetime would throw an error — Merv Merzoug
– Merv Merzoug, Commented May 20, 2020 at 11:28
True. Then FROM instrumentsHistory WHERE instrumentSymbol = 'ZYME' AS f WHERE must be replaced with FROM instrumentsHistory AS f WHERE f.instrumentSymbol = 'ZYME' AND — Akina
– Akina, Commented May 20, 2020 at 11:29

Nimantha · Accepted Answer · 2022-01-06 06:29:59Z

2

This code:

WHERE NOT EXISTS (SELECT 1 FROM instrumentsHistory WHERE instrumentSymbol = 'ZYME' AS f
                  WHERE t.datetime = f.datetime

has multiple errors. There are two WHERE clauses in a row. And as is being used in a WHERE clause. I am guessing this is a copy-and-past error, but it is unclear what you intend.

edited Jan 6, 2022 at 6:29

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered May 20, 2020 at 11:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Merv Merzoug Over a year ago

Thanks Gordon, just added complete query in recent edit

Merv Merzoug Over a year ago

The goal is to insert only new / unique entries in the database. So it will insert the row if the row doesnt already exist in the entire database. I would like to have it evaluate that row against ONLY the rows that have the symbol in the instrumentSymbol, rather than evaluating against every row in the entire database

Collectives™ on Stack Overflow

How to use SQL alias within query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related