The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
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:
     [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