Skip to main content

Delete rows in a table

  • August 22, 2014
  • 2 replies
  • 18 views

Samuel
Hi all, I am facing some problem trying to delete rows from a table through command line with vsql. What I did is:
     dbadmin@linux-vhcg:~> vsql -Atqc "DELETE FROM myTable WHERE col_1='some_value';"

The console is returned with the number of rows matching the condition, but when I try to do a SELECT, the rows were not deleted... I could do the delete only at the adminTools->connect to database, there if I put in the sql statements, it works. Another observation is that I could do a truncate at command line with vsql:
     dbadmin@linux-vhcg:~> vsql -Atqc "TRUNCATE TABLE myTable;"
Above did empty the table.

Hope someone can advise me on how do I get the delete to work. Thanks.

Edit: Using Vertica 7.0.2-1

Edit: (Solved) found the solution in a related post, I should do:
 dbadmin@linux-vhcg:~> vsql -Atqc "DELETE FROM myTable WHERE col_1='some_value'; COMMIT;"

Sorry for spamming...

Samuel

2 replies

Siddarth
Forum|alt.badge.img+1
  • Participating Frequently
  • August 22, 2014
Please commit after deleting the rows.

kushal_alester
Forum|alt.badge.img+1
  • Participating Frequently
  • August 22, 2014
Hi Samuel,

Check the permission given on a table.

Use the below sql query to check the permission given on your vertica table.

SELECT * FROM grants where object_schema = 'Schema_Name' and object_name = 'Table_Name' ORDER BY grantor, grantee;

I often get the same issue and 100% times its because of the privilege given to others on the table.

 
Regards,
Kushal