SQLite3 query issue

Solar2D General Questions/Discussion
#1

I guess SQLite3 should follow the standard SQL statements, so why does the
“DELETE * from tablename” not work?

cheers,

?:slight_smile: [import]uid: 3826 topic_id: 13942 reply_id: 313942[/import]

#2

It won’t work for SQL Server either.
I usually use
[mysql]
delete from tablename where (1=1)
or
truncate table tablename
[/mysql] [import]uid: 71210 topic_id: 13942 reply_id: 51287[/import]

#3

in built failsafe??

cheers,

?:slight_smile: [import]uid: 3826 topic_id: 13942 reply_id: 51288[/import]

#4

It’s not valid SQL, as far as I know. I’ve never used a “Delete *”, always just “Delete From MyTable” to delete records from a table.

Also, there’s no need for:

Delete From MyTable Where (1=1) -- The Where clause is redundant

The absence of a Where clause will cause all records to be deleted. [import]uid: 26769 topic_id: 13942 reply_id: 51299[/import]

#5

Originally SQL have syntax like
Delete * from tablename
its just that its not used in implementation.

@ lordmooch you are right where is actually redundant there. its my mistake. :slight_smile:
its that most of the time I use truncate as its better to use truncate than delete if we want to delete all rows. [import]uid: 71210 topic_id: 13942 reply_id: 51302[/import]

#6

lordmooch / technowand,
thanks for that, I guess it is how the SQL queries were at one time. In fact just to make sure that I was not thinking this up, I did a search for the same and found that Microsoft Syntax is still DELETE [table.*] FROM table WHERE criteria
src: source here

But, I shall keep this in mind.

cheers,

?:slight_smile: [import]uid: 3826 topic_id: 13942 reply_id: 51303[/import]

#7

I use “Truncate” all the time with SQL Server, but there doesn’t appear to be an explicit Truncate within SQLite. Truncate is handy as it resets auto-incrementing columns in SQL Server: wonder if SQLite has anything similar? [import]uid: 26769 topic_id: 13942 reply_id: 51307[/import]