We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Delete rows in a table — Vertica Forum

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:
     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

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