当一次插入多条记录时，Mysql子查询返回的行数超过1

xiozqbni 于 12个月前发布在 Mysql

关注(0)|答案(1)|浏览(127)

我在dr_scan表中有scan_freq列。我试图将记录插入dr_scan表，如果有任何重复的值，它将把重复的行scan_freq列改为0，否则它保持1。
所以我开发了下面提到的代码，它像我预期的那样工作。但是当我试图一次插入多个记录时，它会返回“subquery returns more than 1 row when insert multiple records at once”

if ($savec == "confirm") 
{ 
    $checkIfExistsQuery = "SELECT COUNT(*) as count FROM dr_scan WHERE barcode = (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id' )"; 
    $checkResult = mysqli_query($conn, $checkIfExistsQuery); 
    $rowCount = mysqli_fetch_assoc($checkResult)['count']; 
    if ($rowCount > 0) 
    {
         $updateSql = "UPDATE dr_scan SET scan_freq = 0 WHERE barcode = (SELECT barcode FROM temp_scan_save WHERE user_id='$user_id' )"; 
         $updateResult = mysqli_query($conn, $updateSql); 
     } 
        $insertSql = "INSERT INTO dr_scan (barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, scan_freq) SELECT barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, 1 as scan_frequency FROM temp_scan_save WHERE user_id='$user_id'"; 
        $insertResult = mysqli_query($conn, $insertSql); 
        if (isset($updateResult) && $updateResult && isset($insertResult) && $insertResult)
         { $response = array("response" => "success"); echo json_encode($response); 
        } else 
        { 
            $response = array("response" => "failure"); echo json_encode($response);
         } 
         $sql2 = "DELETE FROM temp_scan_save WHERE user_id='$user_id' "; $result2 = mysqli_query($conn, $sql2); } 
         else { }
mysqli_close($conn);

字符串

mysql

来源：https://stackoverflow.com/questions/77637452/mysql-subquery-returns-more-than-1-row-when-insert-multiple-records-at-once

1条答案

按热度按时间

y0u0uwnf1#

假设barcode是dr_scan中的唯一键，您可以使用一个IODKU和一个DELETE语句。您应该使用prepared statements而不是将字符串连接在一起。

if ($savec == 'confirm') {
    $insertSql = <<<'SQL'
            INSERT INTO dr_scan (barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, scan_freq)
            SELECT barcode, machine_type, line, date, user_id, device_id, center, mstatus, mstatus_changed, live_status, transfer_status, 1 as scan_frequency
            FROM temp_scan_save
            WHERE user_id = ?
            ON DUPLICATE KEY UPDATE scan_freq = 0
            SQL;
    $insertResult = $conn->execute_query($insertSql, [$user_id]);

    echo json_encode(['response' => ($insertResult ? 'success' : 'failure')]);

    $conn->execute_query('DELETE FROM temp_scan_save WHERE user_id = ?', [$user_id]);

} else {}

字符串

赞(0）回复(0）举报 12个月前

我来回答

当一次插入多条记录时，Mysql子查询返回的行数超过1

1条答案

相关问题

热门标签

最新问答