CODEWITHSUNDEEP
mysqlinsertduplicatesodbckey
Peter_Brown_USA
Peter_Brown_USA

Reputation: 41

MySQL Multiple INSERT with ON DUPLICATE KEY UPDATE problems over ODBC

I have search quite extensively here in Stackoverflow but not able to find a solution.

I am trying to INSERT data into a MySQL database via ODBC connection. There is just one PRIMARY key, which is the ID field.

First is a sample code of what works for me without the ON DUPE bits

Query$ = Query$ + "INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES "
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H');"

Now this works fine however I need to add ON DUPLICATE KEY UPDATE to update any existing records, so using this code I would of thought it would work just like the code above.

Query$ = Query$ + "INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES "
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z'," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z'," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z'," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z'," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z'," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H') ON DUPLICATE KEY UPDATE FTG=0, FT='Z';"

But this does not work. Just having the ON DUPLICATE KEY UPDATE FTG=0, FT='Z' etc added just causes errors

Error [MySQL][ODBC 8.0(w) Driver][mysqld-5.7.22]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near

I have also tried ON DUPLICATE KEY UPDATE FTG=VALUES(0), FT=VALUES('Z') without success.

This funny thing is if I use the above codes and paste into phpmyadmin then the inserts are successful, so I just don't know what is causing the error via ODBC

Any ideas?

Upvotes: 0

Views: 186

Answers (1)

Barmar
Barmar

Reputation: 781848

You can't have multiple ON DUPLICATE KEY clauses. Just put one of them at the end, after all the values.

Query$ = Query$ + "INSERT INTO `" + DatabaseMainDataTable.s + "` (`ID`, `Date_TS`, `HTeam`, `ATeam`, `FTG`, `FTA`, `FT`) VALUES "
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')," 
Query$ = Query$ + "(" + GetRandom() + ", 1533340899, 'Peter', 'Frankie', 2, 1, 'H')"
Query$ = Query$ + " ON DUPLICATE KEY UPDATE FTG=0, FT='Z';"

Upvotes: 1

Related Questions