时间戳

首页 > 解决方案 > 尝试在 mysql 中使用重复键

mysql - 尝试在 mysql 中使用重复键

问题描述

我有这个代码

<cfquery >
        INSERT IGNORE INTO tblcustomers(CustomerCode,CustomerID,FirstName,LastName,lid,status) 
        VALUES(<cfqueryparam cfsqltype="varchar" value="#queryData.CustomerCode[currentRow]#">,
            <cfqueryparam cfsqltype="integer" value="#queryData.customerID[currentRow]#">,
            <cfqueryparam cfsqltype="varchar" value="#queryData.firstname[currentRow]#">,
            <cfqueryparam cfsqltype="varchar" value="#queryData.lastname[currentRow]#">,
            <cfqueryparam cfsqltype="integer" value="#queryData.LocationID[currentRow]#">,
            <cfqueryparam cfsqltype="integer" value="#queryData.status[currentRow]#">
            )
        </cfquery>

尝试检查 customerID 是否存在，它应该更新，因为 customerID 是唯一的

任何线索我该怎么做

标签： mysqlcoldfusionlucee

解决方案

如果记录已经存档，您需要告诉数据库该怎么做。

INSERT INTO tblcustomers(CustomerCode,CustomerID,FirstName,LastName,lid,status) 
VALUES
(
        <cfqueryparam cfsqltype="varchar" value="#queryData.CustomerCode[currentRow]#">,
        <cfqueryparam cfsqltype="integer" value="#queryData.customerID[currentRow]#">,
        <cfqueryparam cfsqltype="varchar" value="#queryData.firstname[currentRow]#">,
        <cfqueryparam cfsqltype="varchar" value="#queryData.lastname[currentRow]#">,
        <cfqueryparam cfsqltype="integer" value="#queryData.LocationID[currentRow]#">,
        <cfqueryparam cfsqltype="integer" value="#queryData.status[currentRow]#">
)
ON DUPLICATE KEY UPDATE
CustomerCode = <cfqueryparam cfsqltype="varchar" value="#queryData.CustomerCode[currentRow]#">,
CustomerID = <cfqueryparam cfsqltype="integer" value="#queryData.customerID[currentRow]#">,
FirstName = <cfqueryparam cfsqltype="varchar" value="#queryData.firstname[currentRow]#">,
LastName = <cfqueryparam cfsqltype="varchar" value="#queryData.lastname[currentRow]#">,
lid = <cfqueryparam cfsqltype="integer" value="#queryData.LocationID[currentRow]#">,
status = <cfqueryparam cfsqltype="integer" value="#queryData.status[currentRow]#">

推荐阅读