Assign update query value to variable in SQL

Question

I need to insert some rows to tables in PostgreSQL. But before inserting the row into table, I should check whether the record is existing or not by using update statement. If update statement returns 0 rows, then I can insert, otherwise I can skip that insertion. I should write SQL statements for this in .sql file in PostgreSQL.

How to achieve this?

In Oracle we have below format:

declare
  i number;
begin
  update employees set status = 'fired' where name like '%Bloggs';
  i := sql%rowcount;
IF i ==0 THEN
insert statement here
end

How can I achieve this in Postgres?

Got answer using GET DIAGNOSTICS l = ROW_COUNT; in postgre sql — Balakrishna
– Balakrishna, Commented Aug 5, 2014 at 6:49

Community · Accepted Answer · 2017-05-23 12:20:50Z

1

If concurrency is not a problem for you, and you want to do it in a plpgsql function, rather use the special variable FOUND:

DO
$do$
BEGIN

UPDATE employees SET status = 'fired' WHERE ... ;

IF NOT FOUND THEN
   -- insert statement here
END IF;

END
$do$

Or use a data-modifying CTE:

If concurrency is relevant, read up on proper UPSERT solutions.

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Aug 5, 2014 at 7:03

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Assign update query value to variable in SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related