INSERT ELSE UPDATE a row in a table without primary key

Question

Lets say i have an empty table 'tbl_test' with 3 columns and no primary key; column1 and column2 are string, column3 is int

And i setup a function myFunc(column1Data, column2Data) {

// I expect it to be like this

if column1Data AND column2Data exists in my table, it will update and increment my column3

else if column1Data OR column2Data doesnt exist, it will insert column1Data, column2Data and column3 will set to 0

}

I have tried this solution https://stackoverflow.com/a/4330694

And this is my code: https://www.db-fiddle.com/f/cE1FSJawQJFjSHsk93Lrjt/0

CREATE TABLE tbl_test (
    column1 varchar(255),
    column2 varchar(255),
    column3 int(12)
);

INSERT OR REPLACE INTO tbl_test (column1, column2, column3) 
  VALUES (  COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
            COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2Data'), 'column2Data') ,
            COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2Data'), 0)
          );

INSERT OR REPLACE INTO tbl_test (column1, column2, column3) 
  VALUES (  COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
            COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2Data'), 'column2Data') ,
            COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2Data'), 0)
          );

INSERT OR REPLACE INTO tbl_test (column1, column2, column3) 
  VALUES (  COALESCE((SELECT column1 FROM tbl_test WHERE column1 = 'column1Data'), 'column1Data') ,
            COALESCE((SELECT column2 FROM tbl_test WHERE column2 = 'column2DataNew'), 'column2DataNew') ,
            COALESCE((SELECT SUM(column3+1) AS Total FROM tbl_test WHERE column1 = 'column1Data' AND column2 = 'column2DataNew'), 0)
          );

This will insert new data if either column1Data OR column2Data doesn't exist and will set column3 = 0 which is correct

But if column1Data AND column2Data does exist it will INSERT a new row and increments the column3, instead of updating it

score 0 · Accepted Answer · answered Jan 13 '19 at 13:37

In SQLite 3.24+, you can use upsert. This starts with a unique constraint on the first two columns:

create unique index unq_tbl_test_column1_column2 on tbl_test(column1, column2);

Add a default value of 0 for column3 when you create it.

Then you can express what you want as:

insert into tbl_test (column1, column2)
    values (?, ?)
    on conflict (column1, column2)
        do update set column3 = column3 + 1;

INSERT ELSE UPDATE a row in a table without primary key

1 Answers1