CODEWITHSUNDEEP
vertica
Taras Kovalenko
Taras Kovalenko

Reputation: 2393

Update or remove value Vertica

I have next sql structure table:

id |  account_id  | app_id |  setting  |  value

example data:

  1 | 8fb38bac-6896-49e8-ac92-4cf6300ccd6f | 1      | recipients | [email protected]
  2 | 8fb38bac-6896-49e8-ac92-4cf6300ccd6f | 1      | day        | monday
  3 | 8fb38bac-6896-49e8-ac92-4cf6300ccd6f | 1      | count      | 1234

And need update this data if I send new value or remove if don't exist in request to db.

If I sending (recipients, day, count) with new value update existing data, but if I send just (recipients, day) to update this data and remove count.

I try use next code:

 update settings 
set setting='count', value='1' 
where account_id='8fb38bac-6896-49e8-ac92-4cf6300ccd6f' and app_id='1';

But this script update all field in table. How to fix it?

Upvotes: 0

Views: 530

Answers (1)

Anand S Kumar
Anand S Kumar

Reputation: 90989

From the input , it looks like lots of values in the table have same value for account_id and app_id , hence when you are running your script, it is updating all the values.

If you only want to update rows with count in settings, do -

update settings 
set value='1' 
where account_id='8fb38bac-6896-49e8-ac92-4cf6300ccd6f' and app_id='1' and setting='count' and (value is not null and value <> '');

To remove the row 

delete from settings
account_id='8fb38bac-6896-49e8-ac92-4cf6300ccd6f' and app_id='1' and setting='count' and (value is null or value = '');

Upvotes: 2

Related Questions