CODEWITHSUNDEEP
phpmysql
Jien Wai
Jien Wai

Reputation: 39

Incorrect MySQL query update

I would like to filtering database records if there is duplicate records I will mark the records as deleted = 1. Unfortunately I'm not able to update my records correctly, I did try use limit 1 for updating the records but I only update 1 record only and if I didn't use the limit 1 it will update entire records.

enter image description here

The above is my database table, what I need to do is, assume there is bunch of records with different point_id and I filtered to 1 only. Now I would like to query the records sort by date ASC and update all the records to deleted = 1 expect the last record.

Here is my source code. The problem I facing now is it will update all the records, and if I using LIMIT 1 it only will update 1 record only. 

while($total > 1){
    $total--;
    $sql = sprintf("SELECT * 
        FROM customers_profiles_game_logs 
        WHERE point_id='$points_filter_row[point_id]' 
        AND customer_id='$sql_customer_row[customer_id]'
        ORDER BY date_created ASC");
    $query = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($query);
    $num_rows = mysql_num_rows($query);

    for ($i = 1; $i < $num_rows; $num_rows--) {
       echo $sqli = sprintf("UPDATE customers_profiles_game_logs 
           SET deleted='1' 
           WHERE customer_id='$sql_customer_row[customer_id]'
           AND point_id='$row[point_id]' LIMIT 1"); 
             mysql_query($sqli) or die(mysql_error());
    }
}

Upvotes: 0

Views: 68

Answers (2)

jmail
jmail

Reputation: 6132

you need this:

  UPDATE  customers_profiles_game_logs a
        INNER JOIN
        (
            SELECT  customer_id, MIN(date) date
            FROM    customers_profiles_game_logs
            WHERE   customer_id = 1                -- <== ID HERE
        ) b ON  a.customer_id = b.customer_id
            SET     a.deleted = 1

http://sqlfiddle.com/#!2/6c440/1

another style:

UPDATE  customers_profiles_game_logs a
        INNER JOIN
        (
            SELECT  customer_id, MIN(date) date
            FROM    customers_profiles_game_logs
        ) b ON  a.customer_id = b.customer_id
            SET     a.deleted = 1

http://sqlfiddle.com/#!2/88e7c/1

Upvotes: 0

John Woo
John Woo

Reputation: 263733

You can have a subquery which gets the record to be updated and join it with the table itself, eg.

UPDATE  customers_profiles_game_logs a
        INNER JOIN
        (
            SELECT  customer_id, MIN(date) date
            FROM    customers_profiles_game_logs
            WHERE   customer_id = 1                -- <== ID HERE
        ) b ON  a.customer_id = b.customer_id
                AND a.date = b.date
SET     a.deleted = 1

if you remove the WHERE clause inside the subquery, all the first record for each customer will be updated.

Upvotes: 2

Related Questions