参数化插入...重复键UPDATE失败使用SET语义
我有一个存储客户端信息的数据库。我有一个独特的电话号码字段叫“电话”。我试图使用参数化查询插入到表中,并在重复键上更新客户端信息。我已经四次检查拼写,并且所有内容都达到了标准。我不断收到错误“检查...在WHERE附近使用正确的语法`Phone` =?”参数化插入...重复键UPDATE失败使用SET语义
// These are the fields that we want to Upsert
$fields = "
`Objectives` = :objectives,
`LiquidFunds` = :liquidFunds,
`UnitNumber` = :unitNumber,
`Accredited` = :accredited,
`FirstName` = :firstName,
`LastName` = :lastName,
`Street` = :street,
`Phone` = :phone,
`Email` = :email,
`State` = :state,
`Notes` = :notes,
`City` = :city,
`Zip` = :zip
";
$updateLead = $dbHandle -> prepare("
INSERT INTO `Leads` SET $fields
ON DUPLICATE KEY UPDATE $fields WHERE `Phone` = :phoneKey"
);
后来我绑定的参数,像这样
// Bind our parameters to the updateLead query
$updateLead -> bindParam(":objectives" , $objectives);
$updateLead -> bindParam(":liquidFunds", $_POST['liquidFunds']);
$updateLead -> bindParam(":unitNumber" , $_POST['unitNumber']);
$updateLead -> bindParam(":accredited" , $_POST['accredited']);
$updateLead -> bindParam(":firstName" , $_POST['fname']);
$updateLead -> bindParam(":lastName" , $_POST['lname']);
$updateLead -> bindParam(":street" , $_POST['street']);
$updateLead -> bindParam(":phone" , $_POST['phone']);
$updateLead -> bindParam(":phoneKey" , $_POST['phone']);
$updateLead -> bindParam(":email" , $_POST['email']);
$updateLead -> bindParam(":state" , $_POST['state']);
$updateLead -> bindParam(":notes" , $_POST['notes']);
$updateLead -> bindParam(":city" , $_POST['city']);
$updateLead -> bindParam(":zip" , $_POST['zip']);
这个确切结构完美的作品,如果我更改查询到
$updateLead = $dbHandle -> prepare("UPDATE `Leads` SET $fields WHERE `Phone` = :phoneKey);
我试图把一切都在同一个行,它会抛出相同的错误(只是在不同的“行号”上)。我在这里做错了什么?
PS:我使用PHP/PDO
原来的问题是,我不得不在查询重复占位符的事实。我改变它到这
// These are the fields that we want to Upsert
$fields = "
`Objectives` = :objectives,
`LiquidFunds` = :liquidFunds,
`UnitNumber` = :unitNumber,
`Accredited` = :accredited,
`FirstName` = :firstName,
`LastName` = :lastName,
`Fronter` = :fronter,
`Street` = :street,
`Phone` = :phone,
`Email` = :email,
`State` = :state,
`Notes` = :notes,
`City` = :city,
`Zip` = :zip
";
$updateFields = "
`Objectives` = VALUES(Objectives),
`LiquidFunds` = VALUES(LiquidFunds),
`UnitNumber` = VALUES(UnitNumber),
`Accredited` = VALUES(Accredited),
`FirstName` = VALUES(FirstName),
`LastName` = VALUES(LastName),
`Fronter` = VALUES(Fronter),
`Street` = VALUES(Street),
`Phone` = VALUES(Phone),
`Email` = VALUES(Email),
`State` = VALUES(State),
`Notes` = VALUES(Notes),
`City` = VALUES(City),
`Zip` = VALUES(Zip)
";
$updateLead = $dbHandle -> prepare("
INSERT INTO `Leads` SET $fields
ON DUPLICATE KEY UPDATE $updateFields
");
它的工作!
相同的占位符可以。你的问题是这个查询不支持'WHERE'。 –
真的吗?....为什么呢,如果我可以? MySQL会自动知道哪一行更新? (这样做是有道理的......)。无论如何,我删除了WHERE子句,以及:phoneKey参数。我收到“无效的参数数量”错误。我会继续挖掘。谢谢你的小费 –
你的'Phone'列是唯一的--MySQL绝对知道哪个记录拥有这个值 –
也许是阅读一些关于'DUPLICATE KEY UPDATE'的手册? –
我不确定我是否遵循 –