Question 302907 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql subquery returns more than 1 row when insert multiple records at once

i have scan_freq column in dr_scan table.
I am trying to insert records to the dr_scan table and if there is any duplicate value, it will change the duplicate row scan_freq column to 0 otherwise it stays 1.

so I developed the below-noted code and it worked as I expected.
But when I try to insert multiple records at once with duplicate values it will return " 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);
Tags:

2

Answers


  1. 0

    you’re encountering a problem because the subquery (SELECT barcode FROM temp_scan_save WHERE user_id=’$user_id’) is returning more than one row, and your code is not handling that situation correctly. To resolve this issue, you can modify your SQL queries to handle multiple rows in the subquery.

    if ($savec == "confirm") { 
    $checkIfExistsQuery = "SELECT DISTINCT barcode FROM temp_scan_save WHERE user_id='$user_id'";
    $checkResult = mysqli_query($conn, $checkIfExistsQuery); 

    while ($row = mysqli_fetch_assoc($checkResult)) {
        $barcode = $row['barcode'];

        $updateSql = "UPDATE dr_scan SET scan_freq = 0 WHERE barcode = '$barcode'";
        $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' AND barcode = '$barcode'";

        $insertResult = mysqli_query($conn, $insertSql);

        if (!$updateResult || !$insertResult) {
            $response = array("response" => "failure");
            echo json_encode($response);
            mysqli_close($conn);
            exit; // Exit the script if there's a failure
        }
    }

    $response = array("response" => "success");
    echo json_encode($response);

    $sql2 = "DELETE FROM temp_scan_save WHERE user_id='$user_id'";
    $result2 = mysqli_query($conn, $sql2);
} else {
    // Handle other cases if needed
}

mysqli_close($conn);
    Login or Signup to reply.
  2. 0

    Assuming barcode is a unique key in dr_scan, you can use one IODKU and one DELETE statement. You should be using prepared statements instead of concatenating strings together.

    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 {}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search