Adding to integer value with update or insert statement

user_3429370 0
Viewed 353

I'm trying to create a SQL statement in Firebird 2.5 in order to update an integer quantity by adding to it if the record exists or simply insert a new row. I can't seem to find a real answer to my specific question about adding to a certain quantity if it already exists.

The SQL I could think of is the following :

UPDATE OR INSERT INTO Pencils(Number, Location, Quantity)
VALUES('12345678/90', 'TOP', 20)
MATCHING(Number, Location);

So if there is a matching record I would like to add a certain amount to the actual 20 quantity.

Like I would do with an UPDATE statement :

UPDATE Pencils SET Quantity = Quantity + 20 WHERE Number = '12345678/90'

Is it possible to achieve such result with the UPDATE OR INSERT statement or is there another way? . .

EDIT : Solution for a query used with parameters with IBExpert / Delphi

MERGE INTO Pencils AS Dest
USING (SELECT CAST(:myNumb as varchar(30))  AS Number,
              CAST(:myLoca as varchar(10))  AS Location,
              CAST(:myQuan as integer)  AS Quantity
        FROM RDB$DATABASE) Src
    ON (Dest.Number = Src.Number AND Dest.Location = Src.Location)
WHEN MATCHED THEN
   UPDATE SET Dest.Quantity = Dest.Quantity + Src.Quantity
WHEN NOT MATCHED THEN
   INSERT (Number, Location, Quantity) VALUES (Src.Number, Src.Location, Src.Quantity);
firebird2.5 firebird sql
2 Answers

Use Firebird's MERGE for complex UPSERTs:

MERGE INTO Pencils AS Dest
USING (SELECT '12345678/90' AS Number,
              'TOP'         AS Location,
              20            As Quantity
         FROM RDB$DATABASE) Src
   ON (Dest.Number = Src.Number AND Dest.Location = Src.Location)
WHEN MATCHED THEN
   UPDATE SET Dest.Quantity = Dest.Quantity + 10
WHEN NOT MATCHED THEN
   INSERT (Number, Location, Quantity) VALUES (Src.Number, Src.Location, Src.Quantity);

The only complexity here is your dynamically computed Quantity on UPDATE. If you were simply overwriting an existing record with static values, you could use UPDATE OR INSERT more succinctly.

Example:

SQL> SELECT * FROM Pencils;

NUMBER               QUANTITY LOCATION         
================ ============ ================ 
123                         5 TOP              
123                        10 MID              

SQL> MERGE INTO Pencils AS Dest
CON> USING (SELECT '123' AS Number, 'TOP' AS Location, 20 As Quantity
CON> FROM RDB$DATABASE) Src ON (Dest.Number = Src.Number AND Dest.Location = Src.Location)
CON> WHEN MATCHED THEN
CON>    UPDATE SET Dest.Quantity = Dest.Quantity + 10
CON> WHEN NOT MATCHED THEN
CON>    INSERT (Number, Location, Quantity) VALUES (Src.Number, Src.Location, Src.Quantity);

SQL> SELECT * FROM Pencils;

NUMBER               QUANTITY LOCATION         
================ ============ ================ 
123                        15 TOP              
123                        10 MID              

SQL> SELECT * FROM Pencils;

NUMBER               QUANTITY LOCATION         
================ ============ ================ 
123                         5 TOP              
123                        10 MID              

SQL> MERGE INTO Pencils AS Dest
CON> USING (SELECT '123' AS Number, 'TOP' AS Location, 20 As Quantity
CON> FROM RDB$DATABASE) Src ON (Dest.Number = Src.Number AND Dest.Location = Src.Location)
CON> WHEN MATCHED THEN
CON>    UPDATE SET Dest.Quantity = Dest.Quantity + 10
CON> WHEN NOT MATCHED THEN
CON>    INSERT (Number, Location, Quantity) VALUES (Src.Number, Src.Location, Src.Quantity);

SQL> SELECT * FROM Pencils;

NUMBER               QUANTITY LOCATION         
================ ============ ================ 
123                        15 TOP              
123                        10 MID              
123                        20 BOT
user_1323820

Sure you can use update-or-insert. But you would have to repeat your key values, which would make your query fragile (change only one of two values - and you got misbehaving query)

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);

In Delphi most libraries would not support multiple SQL parameters with the same name, so you would probably have to actually make two parameters, similarly but distinctly named, for this approach to work.

So, all in all, MERGE would be better approach, safer. But for the sake of completeness, this should work too.

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





db<>fiddle here




@pilcrow asks: For completeness, could you show the UPDATE OR INSERT where the newly INSERTed Quantity is not simply "zero + increment"?


Based on NULL semantics in expressions it would just take modifying the middle part of the query like that:


.........
VALUES( '12345678/90', 'TOP', 
  coalesce( 20 + -- increment when found
        ( Select Quantity 
             From Pencils 
          where Number = '12345678/90' 
            and Location = 'TOP' )
       ,  -15 /* new value when not found */ )
       )
.......


          

          

            

              

                
                
                
              

            

            
          

          

            

              


              

            

            

              
                
              
            

            

              

                
                

                  

                    user_9763910
                  

                  
                    
                  
                

              

            

          

          

            


          

        

        
      

      

        


  
Related