Suppose I have a table like this:
Number Product Type
1 Meat Cow
1 Milk A
If I insert a new row:
INSERT INTO t1 (Product, Type) VALUES (Meat, Cow)
I don't want a new row. I want the column "Number" where Product = Meat and Type = Cow be incremented by 1:
Number Product Type
2 Meat Cow
1 Milk A
Is there a SQL command to do this?
You may try the following by using ON CONFLICT that starts from version 9.5 :
create table t1( Number int,
Product varchar(15),
Type varchar(15),
primary key (Product, Type)
);
insert into t1(Number,Product,Type) values(1,'Meat','Cow');
insert into t1(Number,Product,Type) values(1,'Meat','Cow')
on conflict (Product,Type) do update set Number = t1.Number + 1;
select * from t1;
number product type
------ ------- ----
2 Meat Cow
where composite unique(primary) key is a must for Product and Type columns, if not exists than 42P10: there is no unique or exclusion constraint matching the ON CONFLICT specification error raises.
You can use triggers to achieve what you want. Format :
CREATE OR REPLACE TRIGGER trigger_name
INSTEAD OF INSERT ON table_name
WHEN (your_condition_here) --for example, row exist
BEGIN
UPDATE ... --your update query
END;
Create Unique Index on Product and Type, then
INSERT INTO t1 (Product, Type) VALUES (Meat, Cow)
ON CONFLICT ON CONSTRAINT <your_key>
UPDATE set Number = Number + 1
Should work.
(Product, Type)you can useinsert on conflict do update ...