Complex mysql query error with INSERT ON DUPLICATE KEY UPDATE statement

Question

For a web application I'm developing, I have one very complex SQL statement. Which was working before. But I can't figure out what has changed..

The sql error :

"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 'ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid), ownerid = VALUES(' at line 1"

My query is:

<?php
if($bank_name1 !== '') {
        $bank1 = "('$bank_id1', '$owner_id', '$ownertype1', '$accounttype1', '$currency1', '$bank_name1', '$bank_loc1', '$bank_country1', '$bank_accountno1', '$bank_sortcode1', '$bank_iban1', '$bank_bicswift1', '$secondary1'),";
    } else {
        $bank1 = '';
    }

    if($bank_name2 !== '') {
        $bank2 = "('$bank_id2', '$owner_id', '$ownertype2', '$accounttype2', '$currency2', '$bank_name2', '$bank_loc2', '$bank_country2', '$bank_accountno2', '$bank_sortcode2', '$bank_iban2', '$bank_bicswift2', ''),";
    } else {
        $bank2 = '';
    }

    if($bank_name3 !== '') {
        $bank3 = "('$bank_id3', '$owner_id', '$ownertype3', '$accounttype3', '$currency3', '$bank_name3', '$bank_loc3', '$bank_country3', '$bank_accountno3', '$bank_sortcode3', '$bank_iban3', '$bank_bicswift3', '$secondary3'),";
    } else {
        $bank3 = '';
    }

    if($bank_name4 !== '') {
        $bank4 = "('$bank_id4', '$owner_id', '$ownertype4', '$accounttype4', '$currency4', '$bank_name4', '$bank_loc4', '$bank_country4', '$bank_accountno4', '$bank_sortcode4', '$bank_iban4', '$bank_bicswift4', '')";
    } else {
        $bank4 = '';
    }

    $sql = "INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ".$bank1." ".$bank2." ".$bank3." ".$bank4." ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid), ownerid = VALUES(ownerid), ownertype = VALUES(ownertype), accounttype = VALUES(accounttype), currency = VALUES(currency), bankname = VALUES(bankname), location = VALUES(location), bankcountry = VALUES(bankcountry), accountno = VALUES(accountno), sortcode = VALUES(sortcode), iban = VALUES(iban), bicswift = VALUES(bicswift), secondary = VALUES(secondary)";

I have been re-reading this query for an hour now.. I must be missing something rather silly...

Here is the raw SQL query:

INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency,
    bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary)
    VALUES ".$bank1." ".$bank2." ".$bank3." ".$bank4."
ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid),
    ownerid = VALUES(ownerid), ownertype = VALUES(ownertype),
    accounttype = VALUES(accounttype), currency = VALUES(currency),
    bankname = VALUES(bankname), location = VALUES(location),
    bankcountry = VALUES(bankcountry), accountno = VALUES(accountno),
    sortcode = VALUES(sortcode), iban = VALUES(iban),
    bicswift = VALUES(bicswift), secondary = VALUES(secondary)

As long as the UPDATE part of the query is true everything is ok. But when I try to INSERT I get the error thrown by mysql.

Possible duplicate of http://stackoverflow.com/questions/12816117/mysql-on-duplicate-key-update-syntax-error — aldrin27, Sep 10 '15 at 00:44
I have already read this question and the answer. But it seems to me my code is structured in the same way. — Martijn, Sep 10 '15 at 00:48
why are you writing queries like this in 2015, look up prepared statement — meda, Sep 10 '15 at 01:08
looks like your `$bank1` values are not getting substituted. — Rahul, Sep 10 '15 at 01:12

score 2 · Answer 1 · answered Sep 10 '15 at 01:12

2

I add this to the top

$bank_name1="b1";
$bank_name2="b2";
$bank_name3="b3";
$bank_name4="b4";

this to the bottom

echo $sql;

And this is your string:

INSERT INTO bankaccounts (bankaccountid, ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) 
VALUES ('', '', '', '', '', 'b1', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b2', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b3', '', '', '', '', '', '', ''), 
('', '', '', '', '', 'b4', '', '', '', '', '', '', '') 

ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid), ownerid = VALUES(ownerid), ownertype = VALUES(ownertype), 
accounttype = VALUES(accounttype), currency = VALUES(currency), bankname = VALUES(bankname), location = VALUES(location), 
bankcountry = VALUES(bankcountry), accountno = VALUES(accountno), sortcode = VALUES(sortcode), iban = VALUES(iban), 
bicswift = VALUES(bicswift), secondary = VALUES(secondary)

That is one explosion waiting to happen. The Update part should not include VALUES() wrappers.

Rather, the UPDATE part should follow this form:

update col1=someval1,col2=someval2, ...

answered Sep 10 '15 at 01:12

Drew

24,851
10
43
78

Could expand on that a little? Should I use `update col1=someval1,col2=someval2, ...` for each separate variable? – Martijn Sep 10 '15 at 01:18
what compounds the confusion is that you are doing multiple inserts (4 maybe). – Drew Sep 10 '15 at 01:19
what I would try to do is do 1 at a time (one `insert on duplicate key update` at a time). Have the duplicate key clash that would occur be obvious on which row (bank) the update would happen on – Drew Sep 10 '15 at 01:20
Would it be a better plan to make 4 conditional `INSERT .. ON DUPLICATE KEY UPDATE` queries? – Martijn Sep 10 '15 at 01:20
absolutely ! In a heart beat – Drew Sep 10 '15 at 01:21
Thanks a bunch for your insight! – Martijn Sep 10 '15 at 01:22
no problem, if I don't respond, it is because I have to head out. See this from last night, you probably already know it: http://stackoverflow.com/a/32468519/1816093 – Drew Sep 10 '15 at 01:23

score 1 · Accepted Answer · answered Sep 10 '15 at 01:48

Thanks to Drew I got to the following solution. Which in hindsight looks and feels much more robust.

    if($bank_name1 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ('$owner_id', '$ownertype1', '$accounttype1', '$currency1', '$bank_name1', '$bank_loc1', '$bank_country1', '$bank_accountno1', '$bank_sortcode1', '$bank_iban1', '$bank_bicswift1', '$secondary1') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id1', ownerid='$owner_id', ownertype='$ownertype1', accounttype='$accounttype1', currency='$currency1', bankname='$bank_name1', location='$bank_loc1', bankcountry='$bank_country1', accountno='$bank_accountno1', sortcode='$bank_sortcode1', iban='$bank_iban1', bicswift='$bank_bicswift1'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name2 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift) VALUES ('$owner_id', '$ownertype2', '$accounttype2', '$currency2', '$bank_name2', '$bank_loc2', '$bank_country2', '$bank_accountno2', '$bank_sortcode2', '$bank_iban2', '$bank_bicswift2') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id2', ownerid='$owner_id', ownertype='$ownertype2', accounttype='$accounttype2', currency='$currency2', bankname='$bank_name2', location='$bank_loc2', bankcountry='$bank_country2', accountno='$bank_accountno2', sortcode='$bank_sortcode2', iban='$bank_iban2', bicswift='$bank_bicswift2'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name3 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift, secondary) VALUES ('$owner_id', '$ownertype3', '$accounttype3', '$currency3', '$bank_name3', '$bank_loc3', '$bank_country3', '$bank_accountno3', '$bank_sortcode3', '$bank_iban3', '$bank_bicswift3', '$secondary3') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id3', ownerid='$owner_id', ownertype='$ownertype3', accounttype='$accounttype3', currency='$currency3', bankname='$bank_name3', location='$bank_loc3', bankcountry='$bank_country3', accountno='$bank_accountno3', sortcode='$bank_sortcode3', iban='$bank_iban3', bicswift='$bank_bicswift3'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

    if($bank_name4 !== '') {
        $sql = "INSERT INTO bankaccounts (ownerid, ownertype, accounttype, currency, bankname, location, bankcountry, accountno, sortcode, iban, bicswift) VALUES ('$owner_id', '$ownertype4', '$accounttype4', '$currency4', '$bank_name4', '$bank_loc4', '$bank_country4', '$bank_accountno4', '$bank_sortcode4', '$bank_iban4', '$bank_bicswift4') ON DUPLICATE KEY UPDATE bankaccountid='$bank_id4', ownerid='$owner_id', ownertype='$ownertype4', accounttype='$accounttype4', currency='$currency4', bankname='$bank_name4', location='$bank_loc4', bankcountry='$bank_country4', accountno='$bank_accountno4', sortcode='$bank_sortcode4', iban='$bank_iban4', bicswift='$bank_bicswift4'";
        $mysqli->query($sql)or die(mysqli_error($mysqli));
    }

So each bank has its own conditional query.

Complex mysql query error with INSERT ON DUPLICATE KEY UPDATE statement

2 Answers2