Atmesh Mishra Atmesh Mishra - 19 days ago 9
SQL Question

Set flag value in DB query

My query updates a table if a particular ID exists else it inserts a new value.

I want to implement something like -:

if(exists){
update table,
flag = 0}
else{
insert into table,
flag = 1}
return flag;


My existing query is

BEGIN
merge into FCM_DEVICE_REGISTRATION u
using dual
on (device_ad_id = 1)
when matched then
update set fcm_notification_id='N',
last_update_date = SYSDATE
when not matched then
insert (device_ad_id,fcm_notification_id) values (1,'Y');
END;

Answer

When running MERGE you can use SQL%ROWCOUNT to get the number of rows affected. You cannot, however, find out whether an insert or updated was applied or even how many rows were updated and how many inserted. So you must check whether the row in question exists beforehand. And, well, then you know whther to update or insert yourself, so you don't need MERGE anymore.

DECLARE
  v_count integer;
BEGIN
  select count(*) into v_count 
  from fcm_device_registration
  where device_ad_id = 1;

  if v_count = 0 then
    insert into fcm_device_registration 
      (device_ad_id, fcm_notification_id) values (1, 'Y');
  else
    update fcm_device_registration
    set fcm_notification_id = 'N', last_update_date = sysdate
    where device_ad_id = 1;
  end;
END;

The variable v_count contains 0 or 1 (as you say that the device_ad_id is unique in the table). It is 1 for update and 0 for insert. Just the opposite of what you want. However, you can easily derive your flag from this: v_flag := 1 - v_count.