What is the syntax error in this SQLite query?
-
query is:
INSERT INTO FileInfo VALUES ('2021', 'ALex', 'Dunlop') WHERE NOT EXISTS (SELECT 1 FROM FileInfo WHERE EqpCode='2021')
The table has 3 columns. SQLite says: syntax error near WHERE How can I fix it?
-
query is:
INSERT INTO FileInfo VALUES ('2021', 'ALex', 'Dunlop') WHERE NOT EXISTS (SELECT 1 FROM FileInfo WHERE EqpCode='2021')
The table has 3 columns. SQLite says: syntax error near WHERE How can I fix it?
First off, don't INSERT like that - always list the columns into which you want to ISNERT values - if someone (very sensibly) adds an ID column for example, it can really mess up your code, even if the ID column is of IDENTITY type so you can't INSERT to it! Listing the columns in the order you supply their values makes your code safer and easier to maintain in future. Secondly, you can't add a WHERE clause to an INSERT operation because it doesn't affect any existing rows - it only ever adds new ones. If you want to do an insert if no matching rows exist use IF[^] or CASE[^] instead. I'd probably use
COUNT(...) = 0instead ofEXISTS(...)as well."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
First off, don't INSERT like that - always list the columns into which you want to ISNERT values - if someone (very sensibly) adds an ID column for example, it can really mess up your code, even if the ID column is of IDENTITY type so you can't INSERT to it! Listing the columns in the order you supply their values makes your code safer and easier to maintain in future. Secondly, you can't add a WHERE clause to an INSERT operation because it doesn't affect any existing rows - it only ever adds new ones. If you want to do an insert if no matching rows exist use IF[^] or CASE[^] instead. I'd probably use
COUNT(...) = 0instead ofEXISTS(...)as well."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
Do you mean this: INSERT INTO FileInfo (EqpCode, EqpName, FileName) VALUES ('123', 'rty', 'iuo') WHERE NOT EXISTS (SELECT 1 FROM FileInfo IF EqpCode='123') It has the same Error.
-
First off, don't INSERT like that - always list the columns into which you want to ISNERT values - if someone (very sensibly) adds an ID column for example, it can really mess up your code, even if the ID column is of IDENTITY type so you can't INSERT to it! Listing the columns in the order you supply their values makes your code safer and easier to maintain in future. Secondly, you can't add a WHERE clause to an INSERT operation because it doesn't affect any existing rows - it only ever adds new ones. If you want to do an insert if no matching rows exist use IF[^] or CASE[^] instead. I'd probably use
COUNT(...) = 0instead ofEXISTS(...)as well."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I had used a similar query in C# and it was working well (SQL server CE):
mycommand.CommandText = $"INSERT INTO MyData([Wo], [EqN], [Code], [Work], [Cost]) SELECT '{DataGridView3.Rows[i].Cells[0].Value}', '{DataGridView3.Rows[i].Cells[1].Value}', '{DataGridView3.Rows[i].Cells[2].Value}', '{DataGridView3.Rows[i].Cells[3].Value}', '{DataGridView3.Rows[i].Cells[4].Value}' WHERE NOT EXISTS (SELECT 1 FROM MyData WHERE [Wo]='{DataGridView3.Rows[i].Cells[0].Value}' AND [Code]='{DataGridView3.Rows[i].Cells[2].Value}')";
-
Do you mean this: INSERT INTO FileInfo (EqpCode, EqpName, FileName) VALUES ('123', 'rty', 'iuo') WHERE NOT EXISTS (SELECT 1 FROM FileInfo IF EqpCode='123') It has the same Error.
Again, there is no such thing as a WHERE clause on an INSERT statement. Remove everything from WHERE to the end of the statement.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
First off, don't INSERT like that - always list the columns into which you want to ISNERT values - if someone (very sensibly) adds an ID column for example, it can really mess up your code, even if the ID column is of IDENTITY type so you can't INSERT to it! Listing the columns in the order you supply their values makes your code safer and easier to maintain in future. Secondly, you can't add a WHERE clause to an INSERT operation because it doesn't affect any existing rows - it only ever adds new ones. If you want to do an insert if no matching rows exist use IF[^] or CASE[^] instead. I'd probably use
COUNT(...) = 0instead ofEXISTS(...)as well."I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
I found an easy way. I changed EqpCode column type to UNIQUE and Unique Conflict Clause to REPLACE. :) Then:
INSERT INTO FileInfo(EqpCode, EqpName, FileName) VALUES ('123', 'test01', 'test02')
ON CONFLICT(EqpCode) DO UPDATE
SET EqpName = EXCLUDED.EqpName,
FileName = EXCLUDED.FileName; -
I had used a similar query in C# and it was working well (SQL server CE):
mycommand.CommandText = $"INSERT INTO MyData([Wo], [EqN], [Code], [Work], [Cost]) SELECT '{DataGridView3.Rows[i].Cells[0].Value}', '{DataGridView3.Rows[i].Cells[1].Value}', '{DataGridView3.Rows[i].Cells[2].Value}', '{DataGridView3.Rows[i].Cells[3].Value}', '{DataGridView3.Rows[i].Cells[4].Value}' WHERE NOT EXISTS (SELECT 1 FROM MyData WHERE [Wo]='{DataGridView3.Rows[i].Cells[0].Value}' AND [Code]='{DataGridView3.Rows[i].Cells[2].Value}')";
-
query is:
INSERT INTO FileInfo VALUES ('2021', 'ALex', 'Dunlop') WHERE NOT EXISTS (SELECT 1 FROM FileInfo WHERE EqpCode='2021')
The table has 3 columns. SQLite says: syntax error near WHERE How can I fix it?
-
Some have given you the answer but not really shown you what they mean. You can't use WHERE without a SELECT. So, you do something like this:
INSERT INTO Table1(Field1, Field2, ...)
SELECT '2021', 'Alex', ...
WHERE NOT EXISTS (...)DevParty wrote:
You can't use WHERE without a SELECT.
Um ... you sure?
UPDATE MyTable SET MyColumn = @MyValue WHERE MyOtherColumn = @MyOtherValue
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
DevParty wrote:
You can't use WHERE without a SELECT.
Um ... you sure?
UPDATE MyTable SET MyColumn = @MyValue WHERE MyOtherColumn = @MyOtherValue
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
DevParty wrote:
You can't use WHERE without a SELECT.
Um ... you sure?
SQL
Copy Code
UPDATE MyTable SET MyColumn = @MyValue WHERE MyOtherColumn = @MyOtherValue
I'm pretty sure he meant an INSERT, clause, not an UPDATE one! :omg:
-
OriginalGriff wrote:
DevParty wrote:
You can't use WHERE without a SELECT.
Um ... you sure?
SQL
Copy Code
UPDATE MyTable SET MyColumn = @MyValue WHERE MyOtherColumn = @MyOtherValue
I'm pretty sure he meant an INSERT, clause, not an UPDATE one! :omg:
What he may have meant and what he said are not necessarily the same thing! :laugh: Particularly with beginners, you have to be accurate - a bald statement like "WHERE only works with SELECT" is not accurate and can confuse.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony
"Common sense is so rare these days, it should be classified as a super power" - Random T-shirt -
What he may have meant and what he said are not necessarily the same thing! :laugh: Particularly with beginners, you have to be accurate - a bald statement like "WHERE only works with SELECT" is not accurate and can confuse.
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
OriginalGriff wrote:
a bald statement like "WHERE only works with SELECT" is not accurate and can confuse.
Would that be a statement without any hair? :-D
The difficult we do right away... ...the impossible takes slightly longer.
-
OriginalGriff wrote:
a bald statement like "WHERE only works with SELECT" is not accurate and can confuse.
Would that be a statement without any hair? :-D
The difficult we do right away... ...the impossible takes slightly longer.
Wouldn't know - I still have a ponytail. :-D
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
-
DevParty wrote:
You can't use WHERE without a SELECT.
Um ... you sure?
UPDATE MyTable SET MyColumn = @MyValue WHERE MyOtherColumn = @MyOtherValue
"I have no idea what I did, but I'm taking full credit for it." - ThisOldTony "Common sense is so rare these days, it should be classified as a super power" - Random T-shirt AntiTwitter: @DalekDave is now a follower!
