SQL - syntax error in DELETE ALL FROM statement
Hi, It is me again. :( I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :( ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql> Thank you for any hints. Best regards, Radovan -- __________________________ Radovan Bičiště
Hello Radovan, On 08/08/2013 08:48 AM, Radovan Bičiště wrote:
I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :(
ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql>
Confirmed the issue on latest released version (Feb2013-SP3). At the same time, DELETE FROM table; works fine, so I would suggest using this. I removed the non syntax from the web page for now. Best, Hannes
Hi Hannes, Thank you for the prompt reply. Does DELETE FROM without WHERE truncate table storage ? Radovan On 08/08/2013 08:59 AM, Hannes Mühleisen wrote:
Hello Radovan,
On 08/08/2013 08:48 AM, Radovan Bičiště wrote:
I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :(
ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql>
Confirmed the issue on latest released version (Feb2013-SP3). At the same time,
DELETE FROM table;
works fine, so I would suggest using this. I removed the non syntax from the web page for now.
Best,
Hannes
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
-- __________________________ Radovan Bičiště ceos data s.r.o. třída SNP 402/48 500 03 Hradec Králové Czech Republic mobil CZ: +420 601 563 014 skype: rbiciste
Hi Radovan, On 08/08/2013 09:06 AM, Radovan Bičiště wrote:
Thank you for the prompt reply. Does DELETE FROM without WHERE truncate table storage ?
It should, but a quick experiment showed that the files remain at their original size. Let me get back to you. sql>select "count" from storage() where "table"='starships'; +----------+ | count | +==========+ | 68399658 | | 68399658 | | 68399658 | +----------+ 3 tuples (8.771ms) sql>delete from starships; 68399658 affected rows (1.407ms) sql>select "count" from storage() where "table"='starships'; +-------+ | count | +=======+ | 0 | | 0 | | 0 | +-------+ 3 tuples (8.477ms) sql>select location from storage() where "table"='starships'; +-------------+ | location | +=============+ | 03/75/37501 | | 04/01/40101 | | 03/77/37701 | +-------------+ 3 tuples (8.840ms) ➜ acs ls -lah bat/03/77/37701.tail -rw------- 1 hannes da 261M Aug 8 09:11 bat/03/77/37701.tail Best, Hannes
On 08/08/2013 08:59 AM, Hannes Mühleisen wrote:
Hello Radovan,
On 08/08/2013 08:48 AM, Radovan Bičiště wrote:
I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :(
ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql>
Confirmed the issue on latest released version (Feb2013-SP3). At the same time,
DELETE FROM table;
works fine, so I would suggest using this. I removed the non syntax from the web page for now.
Best,
Hannes
_______________________________________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/mailman/listinfo/users-list
Hi Radovan, On 08/08/2013 09:15 AM, Hannes Mühleisen wrote:
On 08/08/2013 09:06 AM, Radovan Bičiště wrote:
Thank you for the prompt reply. Does DELETE FROM without WHERE truncate table storage ?
It should, but a quick experiment showed that the files remain at their original size. Let me get back to you.
There is a workaround for now: sql>create table starships_copy as select * from starships with no data; sql>drop table starships; sql>create table starships as select * from starships_copy with no data; sql>select "location","count" from storage() where "table"='starships'; +-------------+-------+ | location | count | +=============+=======+ | 06/20/62007 | 0 | | 06/00/60077 | 0 | | 06/20/62032 | 0 | +-------------+-------+ ➜ acs ls -lah bat/06/00/60067.tail -rw------- 1 hannes da 0 Jul 19 09:15 bat/06/00/60067.tail Hope this helps. Best, Hannes
On 08/08/2013 08:59 AM, Hannes Mühleisen wrote:
Hello Radovan,
On 08/08/2013 08:48 AM, Radovan Bičiště wrote:
I'm trying to "truncate" according to http://www.monetdb.org/Documentation/SQLreference/Updates using DELETE ALL FROM statement. But what I get is syntax error. :(
ceos@lux-analytics:~$ mclient -h localhost -d zkdev1 -u drmaxtst -p 50000 password: Welcome to mclient, the MonetDB/SQL interactive terminal (Feb2013-SP3) Database: MonetDB v11.15.11 (Feb2013-SP3), 'mapi:monetdb://lux-analytics:50000/zkdev1' Type \q to quit, \? for a list of available commands auto commit mode: on sql>delete all from crmkarta; syntax error, unexpected ALL, expecting FROM in: "delete all" sql>
Confirmed the issue on latest released version (Feb2013-SP3). At the same time,
DELETE FROM table;
works fine, so I would suggest using this. I removed the non syntax from the web page for now.
On 08/08/2013 09:15 AM, Hannes Mühleisen wrote:
Hi Radovan,
On 08/08/2013 09:06 AM, Radovan Bičiště wrote:
Thank you for the prompt reply. Does DELETE FROM without WHERE truncate table storage ?
No it only marks them as deleted. sql>create table tmp(i int); operation successful (28.215ms) sql>insert into tmp values(1),(2); 2 affected rows (22.792ms) sql>delete from tmp; 2 affected rows (15.135ms) sql>select * from storage() where "table" ='tmp'; +--------+-------+--------+------+----------+-------+--------+--------+--------+---------+--------+ | schema | table | column | type | location | count | typewi | column | heapsi | indices | sorted | : : : : : : : dth : size : ze : : : +========+=======+========+======+==========+=======+========+========+========+=========+========+ | sys | tmp | i | int | 21/2112 | 0 | 4 | 0 | 0 | 0 | true | +--------+-------+--------+------+----------+-------+--------+--------+--------+---------+--------+ 1 tuple (4.572ms) And the file structure: [.......bat]$ ls 01 07 11 14 16 21 3.tail 4.head 4.theap HC 05 10 12 15 20 3.head 3.theap 4.tail BACKUP LEFTOVERS [........bat]$ ls 21 2100.tail 2102.tail 2104.tail 2113.tail 2115.tail 2121.tail 2124.tail So file seems gone
participants (3)
-
Hannes Mühleisen -
Martin Kersten -
Radovan Bičiště