Page 1 of 1

Sql code is not working correctly

Posted: Fri Nov 16, 2018 6:51 pm
by Skynet_IV
Hello All,
I have little problem with my code and i need advice how to make it work right way.
In my code i am collecting IDs to array in loop. Final array is transformed to string "scorersStr" and can looks like here {1,2,2,1,8,9,5,2}

Finally I am increasing values for this IDs

Code: Select all

wxString sql88;
sql88.Printf("update players set goals = goals +1 where id_player in (" + scorersStr + ")" );
wxSQLite3ResultSet Res88 = this->DB->ExecuteQuery(sql88);
PROBLEM is that value goals for IDs which are presented more then once is increased only once. So for array "scorersStr" = {1,2,2,1,8,9,5,2} is value goal for ID =2 increased once. I need increase it by 3 in this case. I can do separate SQL queries and increase values separatelly, but in that case is execution time very very very long because this action is performed for every ID from different table. So i need to do it in one query.

My question: Is there way how to perform this task correctly in one query?? Thanks

Re: Sql code is not working correctly

Posted: Fri Nov 16, 2018 8:41 pm
by ONEEYEMAN
Hi,
This has nothing to do with the wxWidgets.
The very first question you should ask - what do you expect to see after executing the query against the DB. Second write the data that is currently in the table/DB and write the data that you want to have.
Perform the transition on the paper.
Write the appropriate query.
Test it.
Commit it.

Thank you.

Re: Sql code is not working correctly

Posted: Sat Nov 17, 2018 5:11 pm
by Skynet_IV
You are right ONEEYEMAN. I am sorry for this, I am new here. I just wanted to kindly ask here for help as I am registered here. I have No problem with deleting this topic after that.

Clarification:

Code: Select all

wxString sql88;
sql88.Printf("update players set goals = goals +1 where id_player in (1,2,2,1,8,9,5,2)" );
wxSQLite3ResultSet Res88 = this->DB->ExecuteQuery(sql88);
IDs which are presented in () more than once e.g. 1,2, are in this sql code increased by 1 only once. I expected to be icreased so many times how many times are presented there. So if player ID 2 has 0 goals before, after execution code above should have 3, but actualy have 1. So this SQL increase values max by 1, no matter how many times are ID presented. MayBe problem with SQL. Can somebody give me advice to reach my goal?

Re: Sql code is not working correctly

Posted: Sat Nov 17, 2018 5:45 pm
by ONEEYEMAN
Hi,
I order to do that you need to create more code as it wil be easier in C++ than going SQL route.
Just do a loop increasing the value and then do the update inside the loop.

Will be much easier than trying to poke around with SQL.

Thank you.

Re: Sql code is not working correctly

Posted: Sat Nov 17, 2018 5:47 pm
by doublemax
I have only basic SQL knowledge and i don't know a way to solve this on the SQL side only.

However, instead of sending a SQL update for each entry in the list, i would preprocess the list and sort them by number of occurences.

In your example that would result in 3 sql updates:

Code: Select all

update players set goals = goals +1 where id_player in (8,9,5)
update players set goals = goals +2 where id_player in (1)
update players set goals = goals +3 where id_player in (2)

Re: Sql code is not working correctly

Posted: Sat Nov 17, 2018 7:16 pm
by Skynet_IV
Yes, This can be possible solution for me. Thanks a lot both for answers.

Now if you wish to delete this topic as its not related to wxWidges, you can delete it. :)

Re: Sql code is not working correctly

Posted: Sat Nov 17, 2018 7:22 pm
by ONEEYEMAN
Hi,
Yes, it might be a solution, if the nuimber of updates is manageable.
However, if the number of records is around 100, it is much better to do that in a lop.

Thank you.