CODEWITHSUNDEEP
c++mysqlqtsqlite
uchar
uchar

Reputation: 2590

replace column values of table if exists

I have two table :

Main:

Id    | Count | Type
name1    3       1
name2    5       2
...

Temp:
Id     |Count|
name1     7
name3     2

I want to update Count column of Main table if Id exists in both table .

So after doing some operations result for above example should be :

Main:
Id    | Count | Type
name1    7       1
name2    5       2

My current code is :

QSqlQuery search_query = database_.exec("SELECT * From Temp");
while (search_query.next()){
    database_.exec("UPDATE Main"
        " SET Count=" + search_query.value(1).toString() +
        " WHERE Id = '" + search_query.value(0).toString() + "' ;");
}
database_.exec("DELETE FROM Temp");

It works fine but it's very slow !! (for 500 rows it take 2-3 minutes !!)

I'm looking for more efficient way . What should I do ?

Upvotes: 0

Views: 198

Answers (2)

CL.
CL.

Reputation: 180080

Inside an UPDATE statement, you can look up new values with a correlated subquery:

UPDATE Main
SET Count = (SELECT Count FROM Temp WHERE Id = Main.Id)
WHERE Id IN (SELECT Id FROM Temp)

Upvotes: 1

DobleA
DobleA

Reputation: 334

How about calling a single update script to the database?

UPDATE Main m
INNER JOIN Temp t ON
    m.Id = t.Id
SET m.Count = t.Count

Upvotes: 1

Related Questions