php – INSERT ON DUPLICATE KEY UPDATE语句的复杂mysql查询错误

发布时间：2020-12-13 16:58:03 所属栏目：PHP教程来源：网络整理

导读：对于我正在开发的Web应用程序,我有一个非常复杂的SQL语句.哪个以前工作过.但我无法弄清楚改变了什么.. sql错误： “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use ne

对于我正在开发的Web应用程序,我有一个非常复杂的SQL语句.哪个以前工作过.但我无法弄清楚改变了什么..

sql错误：

“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″

我的查询是：

<?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','$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','$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','$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)";

我现在已经重新阅读了这个查询一小时了……我一定是错过了一些相当愚蠢的东西……

这是原始SQL查询：

INSERT INTO bankaccounts (bankaccountid,secondary)
    VALUES ".$bank1." ".$bank2." ".$bank3." ".$bank4."
ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid),secondary = VALUES(secondary)

只要查询的UPDATE部分为真,一切都可以.但是当我尝试INSERT时,我得到了mysql抛出的错误.

解决方法

我把它添加到顶部

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

这到底

echo $sql;

这是你的字符串：

INSERT INTO bankaccounts (bankaccountid,secondary) 
VALUES ('','','b1',('','b2','b3','b4','') 

ON DUPLICATE KEY UPDATE bankaccountid = VALUES(bankaccountid),secondary = VALUES(secondary)

那是一场等待发生的爆炸. Update部分不应包含VALUES()包装器.

相反,UPDATE部分应遵循以下形式：

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!