Firebird 3.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


3.0.5
UTF8




create table Pencils( Number varchar(20), Location varchar(10), Quantity int,
   constraint pk_pens primary key (number, location) )


INSERT INTO Pencils(Number, Location, Quantity)
VALUES('12345678/90', 'TOP', 13)
1 rows affected

INSERT INTO Pencils(Number, Location, Quantity)
VALUES('12345678/90', 'MID', 7)
1 rows affected

select * from pencils


NUMBER
LOCATION
QUANTITY


12345678/90
TOP
13

12345678/90
MID
7




UPDATE OR INSERT 
   INTO Pencils(Number, Location, Quantity)
VALUES( '12345678/90', 'TOP', 
  20 + coalesce(
          (Select Quantity 
             From Pencils 
          where Number = '12345678/90' 
            and Location = 'TOP')
       ,0 )
       )
MATCHING(Number, Location);
1 rows affected

UPDATE OR INSERT 
   INTO Pencils(Number, Location, Quantity)
VALUES( '12345678/90', 'BOTTOM', 
  20 + coalesce(
          (Select Quantity 
             From Pencils 
          where Number = '12345678/90' 
            and Location = 'BOTTOM')
       ,0 )
       )
MATCHING(Number, Location);
1 rows affected

select * from pencils


NUMBER
LOCATION
QUANTITY


12345678/90
TOP
33

12345678/90
MID
7

12345678/90
BOTTOM
20