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
[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
0
Comments
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