Sql code is not working correctly

In this forum you can discuss database related issues which can be wxWidgets related, but also generic in nature.
Post Reply
Skynet_IV
In need of some credit
In need of some credit
Posts: 5
Joined: Sun Oct 21, 2018 7:32 am

Sql code is not working correctly

Post 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
ONEEYEMAN
Part Of The Furniture
Part Of The Furniture
Posts: 7449
Joined: Sat Apr 16, 2005 7:22 am
Location: USA, Ukraine

Re: Sql code is not working correctly

Post 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.
Skynet_IV
In need of some credit
In need of some credit
Posts: 5
Joined: Sun Oct 21, 2018 7:32 am

Re: Sql code is not working correctly

Post 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?
ONEEYEMAN
Part Of The Furniture
Part Of The Furniture
Posts: 7449
Joined: Sat Apr 16, 2005 7:22 am
Location: USA, Ukraine

Re: Sql code is not working correctly

Post 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.
User avatar
doublemax
Moderator
Moderator
Posts: 19102
Joined: Fri Apr 21, 2006 8:03 pm
Location: $FCE2

Re: Sql code is not working correctly

Post 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)
Use the source, Luke!
Skynet_IV
In need of some credit
In need of some credit
Posts: 5
Joined: Sun Oct 21, 2018 7:32 am

Re: Sql code is not working correctly

Post 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. :)
ONEEYEMAN
Part Of The Furniture
Part Of The Furniture
Posts: 7449
Joined: Sat Apr 16, 2005 7:22 am
Location: USA, Ukraine

Re: Sql code is not working correctly

Post 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.
Post Reply