Sqlite delete query error

Question

 delete N.* from Tbl_Nodes N, Data_Tree DT WHERE N.Part = DT.Part

for this command I am getting following error.

System.Data.SQLite.SQLiteException: SQLite error near "N": syntax error

Above command works fine for MSAccess.

Is there any alternative to use table shortcut in Sqlite?

That's some rather non-standard SQL. What does WHERE N.Part mean, and how are Tbl_Nodes and Data_Tree correlated? — Marcelo Cantos
– Marcelo Cantos, Commented Nov 18, 2011 at 10:40

Larry Lustig · Accepted Answer · 2014-08-25 12:50:26Z

4

The DELETE statement operates on a single table and does not use a table alias. Therefore, your FROM clause must read FROM Tbl_Nodes.

You're probably looking for:

 delete from Tbl_Nodes WHERE Part IN (SELECT Part FROM Data_Tree)

Note that this will remove all nodes from Tbl_Nodes that have a corresponding Part value in Data_Tree but does not remove any records from Data_Tree itself.

While SQL varies somewhat among vendors, as a general principle it's a mistake to learn SQL from MS Access and try to apply it to other products. MS Access features some very non-standard constructions.

edited Aug 25, 2014 at 12:50

answered Nov 18, 2011 at 11:02

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

2 Comments

Donal Fellows Over a year ago

+1 for the only solution so far that even stands a chance of being correct (as opposed to a syntax error!) SQLite sticks much closer to the SQL standard in much of its syntax than most databases.

Nelson Over a year ago

the actual alias syntax table AS alias works with DELETE. It's just when you've forgotten more compatible SQL syntax and you do table alias where it fails on DELETE. Weirdly, it does work in SELECTs.

seth · Accepted Answer · 2011-11-18 10:39:33Z

1

Using an alias for the table?

FROM table AS t1

answered Nov 18, 2011 at 10:39

seth

1,3851 gold badge17 silver badges29 bronze badges

Comments

Chris W · Accepted Answer · 2011-11-18 10:41:44Z

-1

You're missing a bit of your SQL statement there I guess but does it not work if you just say:

delete N from Tbl_Nodes N, Data_tree DT WHERE...(rest of statement)

I've just removed the .*

answered Nov 18, 2011 at 10:41

Chris W

3,3143 gold badges28 silver badges29 bronze badges

1 Comment

Chris W Over a year ago

From what I find on the sqlite site it seems that doing deletes against joins as you can in other DB products is not supported. The Larry Lustig answer should work for you though.

Collectives™ on Stack Overflow

Sqlite delete query error

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related