Need to update if exist or insert the values

Question

I need to update the price of the brick if there is already a brick in the purchased table, else I have to insert it. I wrote this code:

DECLARE 
    colour VARCHAR2(10) :='brown';
    shape VARCHAR2(10) :='rectangle';
    price NUMBER(10,2) :=21;

BEGIN 
    UPDATE purchased_bricks pb
    SET pb.price = price
    WHERE pb.colour = colour
    AND pb.shape = shape;

    IF sql%rowcount = 0 
        THEN
        INSERT INTO purchased_bricks
        VALUES(colour,shape,price);
    END IF;
END;

The first time I ran it, the record was inserted. Then when I tried to update the price of the brick the change was not reflected in the table.

Answer 1

You can make a count on your desired filter criteria and if it is greater than 0 than update it else insert the new row;something like below :

DECLARE 
        colour VARCHAR2(10) :='brown';
        shape VARCHAR2(10) :='rectangle';
        price NUMBER(10,2) :=21;
        count_row Number;

      BEGIN 
        Select count(*) into count_row from purchased_bricks pb
        WHERE pb.colour = colour
        AND pb.shape = shape;

        IF count_row > 0
        THEN
        UPDATE purchased_bricks pb
        SET pb.price = price
        WHERE pb.colour = colour
        AND pb.shape = shape;

         ELSE

            INSERT INTO purchased_bricks
            VALUES(colour,shape,price);
        END IF;
    END;

Answer 2

I found it. The problem was that the variable names were the same as the column names. So set pb.price = price updated the table with the existing table value not the variable value. The solution is to make the names different, say by using a prefix.

DECLARE 
    l_colour VARCHAR2(10) :='brown';
    l_shape VARCHAR2(10) :='rectangle';
    l_price NUMBER(10,2) :=21;

BEGIN 
    UPDATE purchased_bricks pb
    SET pb.price = l_price          
    WHERE pb.colour = l_colour                
    AND pb.shape = l_shape;

    IF sql%rowcount = 0 
        THEN
        INSERT INTO purchased_bricks
        VALUES(colour,shape,price);
    END IF;
END;

Answer 3

Instead of PL/SQL, I'd suggest MERGE which is also known as UPSERT (UPdate and/or inSERT).

Here's an example:

SQL> CREATE TABLE purchased_bricks
  2  (
  3     colour   VARCHAR2 (10),
  4     shape    VARCHAR2 (20),
  5     price    NUMBER
  6  );

Table created.

SQL> MERGE INTO purchased_bricks p
  2       USING (SELECT 1 FROM DUAL) x
  3          ON (    p.colour = '&&par_colour'
  4              AND p.shape = '&&par_shape')
  5  WHEN MATCHED
  6  THEN
  7     UPDATE SET p.price = &&par_price
  8  WHEN NOT MATCHED
  9  THEN
 10     INSERT     (colour, shape, price)
 11         VALUES ( '&&par_colour', '&&par_shape', &&par_price);
Enter value for par_colour: brown
Enter value for par_shape: rectangular
Enter value for par_price: 21

1 row merged.

SQL> select * From purchased_bricks;

COLOUR     SHAPE                     PRICE
---------- -------------------- ----------
brown      rectangular                  21

Let's try a different price:

SQL> undefine par_price
SQL> MERGE INTO purchased_bricks p
  2       USING (SELECT 1 FROM DUAL) x
  3          ON (    p.colour = '&&par_colour'
  4              AND p.shape = '&&par_shape')
  5  WHEN MATCHED
  6  THEN
  7     UPDATE SET p.price = &&par_price
  8  WHEN NOT MATCHED
  9  THEN
 10     INSERT     (colour, shape, price)
 11         VALUES ( '&&par_colour', '&&par_shape', &&par_price);
Enter value for par_price: 50

1 row merged.

SQL> select * from purchased_bricks;

COLOUR     SHAPE                     PRICE
---------- -------------------- ----------
brown      rectangular                  50

SQL>

The above example is adjusted so that it works in SQL*Plus. Depending on tool you use, parameters' values might be referenced differently, such as

MERGE INTO purchased_bricks p
     USING (SELECT 1 FROM DUAL) x
        ON (    p.colour = :par_colour
            AND p.shape = :par_shape)
WHEN MATCHED
THEN
   UPDATE SET p.price = :par_price
WHEN NOT MATCHED
THEN
   INSERT     (colour, shape, price)
       VALUES ( :par_colour, :par_shape, :par_price);