错误代码：1093。您无法在FROM子句中为更新指定目标表'mm'

Question

i google this question several time but failed to fix mentioned issue. please help me.

My query:

UPDATE meter As mm SET mm.mdi_reset_date='2', mm.write_mdi_reset_date='1'
            WHERE mm.msn IN (SELECT m.msn
            FROM meter m
                INNER JOIN connection con ON con.feeder_id = m.feeder_id
                INNER JOIN consumers co ON co.consumer_id = con.customer_id 
                INNER JOIN customer cs ON co.parent_id = cs.customer_id 
                INNER JOIN cs_group_details cd ON cd.customer_id=cs.customer_id 
                WHERE co.batch_no = '30'
            AND cd.cs_group_id='4');

Answer 1

First Create temporary table using query

CREATE TABLE meter_temp LIKE meter;

Now insert data from meter to temporary table meter_temp

INSERT INTO meter_temp SELECT * FROM meter;

Use your update query

UPDATE meter As mm SET mm.mdi_reset_date='2', mm.write_mdi_reset_date='1'
            WHERE mm.msn IN (SELECT m.msn
            FROM meter_temp m
                INNER JOIN connection con ON con.feeder_id = m.feeder_id
                INNER JOIN consumers co ON co.consumer_id = con.customer_id 
                INNER JOIN customer cs ON co.parent_id = cs.customer_id 
                INNER JOIN cs_group_details cd ON cd.customer_id=cs.customer_id 
                WHERE co.batch_no = '30'
            AND cd.cs_group_id='4');

Let me know if it is working or not.

Edited ::

$subQuery = "SELECT m.msn FROM meter_temp m INNER JOIN connection con ON con.feeder_id = m.feeder_id INNER JOIN consumers co ON co.consumer_id = con.customer_id INNER JOIN customer cs ON co.parent_id = cs.customer_id INNER JOIN cs_group_details cd ON cd.customer_id=cs.customer_id WHERE co.batch_no = '30' AND cd.cs_group_id='4'";
$result = mysqli($con,$query); // assuming you are using mysqli. change as per your code
while($row = mysqli_fetch_assoc($result))
{
   $msnArray[] = $row['msn'];
}
$msn = implode(",",$msnArray); // assuming msn is integer and not string.
// if it is string then you have to wrap it in quotes.
// Now run update query
$update = "UPDATE meter SET mdi_reset_date='2', write_mdi_reset_date='1' WHERE msn IN ({$msn})";
$updateResult = mysqli_query($con,$update);