1

In the below code, I have created a table "one" which is part of a QSQLITE database with attributes as shown in the code below. Then I am inserting QStrings a1,b1,c1,d1 into the table which are user inputted. Now the software must insert into the database only if b1 and c1 are not already present in the table. If b1 and c1 are present in the table then the d1 column value must be updated to the sum of the already present d1 value and the new d1 value.

[Eg: consider,

(row1) "A B C D" (row2) "fruit apple red 10" (row3) "fruit banana yellow 15"

as the rows inserted into the database. Now if i have to insert another row "fruit apple red 8" , the it must update the D column of first row of the table as 10+8 = "18". How do I go about this? 

@   
QSqlQuery query;
    query.prepare("create table one (A varchar(20), B varchar(30), 
    C varchar(30), D integer(10))"); 
    query.exec();

    query.prepare("insert into one (A,B,C,D)" 
    "values(:a,:b,:c,:d)");
    query.bindValue(":a",a1);
    query.bindValue(":b",b1);
    query.bindValue(":c",c1);
    query.bindValue(":d",d1);
@
annie
  • 143
  • 1
  • 1
  • 11

1 Answers1

0

Try to update the existing row. If that did not work, the row does not exist, and you have to insert it:

query.prepare("UPDATE one SET d1 = d1 + :d WHERE ...");
query.bindValue(...);
query.exec();
if (query.numRowsAffected() == 0) {
    query.prepare("INSERT ...");
    query.bindValue(...);
    query.exec();
}
CL.
  • 173,858
  • 17
  • 217
  • 259
  • How do i check if "fruit apple red" is already present before updating? – annie Aug 12 '17 at 13:36
  • The UPDATE already searches for this row. `numRowsAffected()` tells you whether such a row was found. – CL. Aug 12 '17 at 13:47
  • query.prepare("UPDATE one SET d1 = d1 + :d WHERE A=:a, B=:b, C=:c"); query.bindValue(":a",a1); query.bindValue(":b",b1); query.bindValue(":c",c1); query.exec(); @ Is this query correct? – annie Aug 12 '17 at 14:29