I have written a query which will check for a record existence, If condition is true, it will update else it will insert a new record. The problem is while inserting, It returns
Query returned successfully: 1 rows affected, 200ms execution time.
but when updating It returns
Query returned successfully: 0 rows affected, 190 ms execution time.
but the value is updated correctly.
here is the sample script to create table
CREATE TABLE sample
(
templateid integer NOT NULL DEFAULT nextval('checktemplate_language_lookup_seq'::regclass),
languageid integer NOT NULL,
templatetitle character varying(100),
disclaimer text,
createdby integer NOT NULL,
createdtimestamp timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp without time zone,
updatedby integer,
updatedtimestamp timestamp without time zone,
CONSTRAINT sample_templateid_languageid UNIQUE (templateid, languageid)
)
To Insert new record in a table :
WITH new_values (templateId, languageId, templatetitle, disclaimer, createdby,createdtimestamp,updatedby,updatedtimestamp)
AS (
VALUES (1, 1, 'LangTemplateTitle1', 'LangDisclaimer1', 1,current_timestamp,1,current_timestamp)
), upsert
AS (
UPDATE sample m
SET templatetitle = nv.templatetitle, disclaimer = nv.disclaimer, updatedby = nv.updatedby, updatedTimeStamp = nv.updatedtimestamp
FROM new_values nv
WHERE m.templateId = nv.templateId AND m.languageId = nv.languageId RETURNING m.*
)
INSERT INTO sample (templateId, languageId, templatetitle, disclaimer, createdby,createdtimestamp)
SELECT templateId, languageId, templatetitle, disclaimer, createdby ,createdtimestamp
FROM new_values
WHERE NOT EXISTS (
SELECT 1
FROM upsert up
WHERE up.templateId = new_values.templateId AND up.languageId = new_values.languageId
)
To update the same row with different values:(same query value updated)
WITH new_values (templateId, languageId, templatetitle, disclaimer, createdby,createdtimestamp,updatedby,updatedtimestamp)
AS (
VALUES (1, 1, 'LangTemplateTitle2', 'LangDisclaimer2', 1,current_timestamp,1,current_timestamp)
), upsert
AS (
UPDATE sample m
SET templatetitle = nv.templatetitle, disclaimer = nv.disclaimer, updatedby = nv.updatedby, updatedTimeStamp = nv.updatedtimestamp
FROM new_values nv
WHERE m.templateId = nv.templateId AND m.languageId = nv.languageId RETURNING m.*
)
INSERT INTO sample (templateId, languageId, templatetitle, disclaimer, createdby,createdtimestamp)
SELECT templateId, languageId, templatetitle, disclaimer, createdby ,createdtimestamp
FROM new_values
WHERE NOT EXISTS (
SELECT 1
FROM upsert up
WHERE up.templateId = new_values.templateId AND up.languageId = new_values.languageId
)
How do i get "no of rows affected value" even I do updation with the same query
What I have tried:
I just found on simple googling,
RETURNING
will return no of row afftected, but its available in above query.