Сложная ошибка запроса MySQL с оператором INSERT ON DUPLICATE KEY UPDATE
Для разрабатываемого веб-приложения у меня есть один очень сложный оператор SQL. Который работал раньше. Но я не могу понять, что изменилось ..
Ошибка SQL:
«У вас есть ошибка в вашем синтаксисе SQL; проверьте правильность синтаксиса в руководстве, соответствующем вашей версии сервера MySQL, для использования около ON ON DUPLICATE KEY UPDATE bankaccountid = VALUES (bankaccountid), ownerid = VALUES (‘в строке 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', '$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)";
Я перечитываю этот запрос уже час … Должно быть, я что-то упустил довольно глупо …
Вот необработанный запрос 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)
Пока UPDATE часть запроса верна все ок. Но когда я пытаюсь INSERT Я получаю ошибку, выданную MySQL.
Решение
Я добавляю это к вершине
$bank_name1="b1";
$bank_name2="b2";
$bank_name3="b3";
$bank_name4="b4";
это до дна
echo $sql;
И это ваша строка:
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)
Это один взрыв, ожидающий случиться. Обновление часть не следует включите VALUES () оболочки.
Скорее, часть ОБНОВЛЕНИЯ должна следовать за этой формой:
update col1=someval1,col2=someval2, ...
Другие решения
Благодаря Дрю я получил следующее решение. Который в ретроспективе выглядит и чувствует себя намного более крепким.
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));
}
Так что у каждого банка свой условный запрос.