Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Delete rows in a table

Hi all, I am facing some problem trying to delete rows from a table through command line with vsql. What I did is:
     dbad[email protected]:~> 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:
     [email protected]:~> 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:
 [email protected]:~> vsql -Atqc "DELETE FROM myTable WHERE col_1='some_value'; COMMIT;"

Sorry for spamming...

Samuel

Comments

  • Please commit after deleting the rows.
  • 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


Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.