CODEWITHSUNDEEP
sqldatabasepostgresql
Rogger Fernandes
Rogger Fernandes

Reputation: 915

sql increment value if exists otherwise insert with value 1

Given following schema:

document(id, path);
term(id, description);
term_document(id_term,id_document,frequency);

Given a document path and term description, if I don't have a record of the term and the document in the table term_documento I want to insert into the table term_document with frequency = 1, otherwise, I want to just increment the frequency value.

What I came up so far is this:

insert into term_document(id_term, id_document, frequency)
select term.id, document.id, 1
from term, document where term.description='to' and document.path='/home/rogger/Projetos/projeto-ori-ufu/src/main/resources/d1.txt'

which satisties the case I dont have a record in term_document, but I don't know how to increment to satisfy both.

Upvotes: 0

Views: 907

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39497

Assuming you have a unique constraint on term_document(id_term, id_document), you can use on conflict clause in your insert:

insert into term_document(id_term, id_document, frequency)
select t.id, d.id, 1
from term t
cross join document d
where t.description = 'to'
    and d.path = '/home/rogger/Projetos/projeto-ori-ufu/src/main/resources/d1.txt'
on conflict (id_term, id_document) do update set frequency = frequency + 1;

I used cross join as you did (only in modern syntax). If there is actually a relation between the two table, you'd need to join them on those columns instead.

Upvotes: 1

Related Questions