CODEWITHSUNDEEP
phpmysqlpdo
Malca
Malca

Reputation: 123

Insert process after On Duplicate Key Update

Example i have temporary table 1 (Home_Temp) with field (home_id, home_name) and contain data. I want to insert all data from table 1 to table 2 (Home_History). Table 2 consist field (home_id, home_name, status). The default value for 'status' is 1. The primary key for Home_Temp is home_id while Home_History is home_id and status.

For the first time insert process, data can be insert from table 1 to table 2 with default value for 'status' is 1. For second time insert process with same home_id, i want to insert the home_name and status to '1'. But the old data with same home_id and home_name i want to update to status '0'. Meaning the latest home_id have status value '1'. The old home_id have status value '0'. The code is successful until update the status for old home_id to '0'.

$stmt = $dbcon->prepare("INSERT INTO Home_History (home_id, home_name) SELECT home_id, home_name FROM Home_Temp ON DUPLICATE KEY UPDATE status = '0'");
$stmt->execute();

But how to insert the latest data? I try to do code below but it seems nonsense and not working.

$stmt = $dbcon->prepare("INSERT INTO Home_History (home_id, home_name) SELECT home_id, home_name FROM Home_Temp ON DUPLICATE KEY UPDATE status = '0' AND INSERT INTO Home_History (home_id, home_name) SELECT home_id, home_name FROM Home_Temp");
$stmt->execute();

How can i make sure the latest insert data has status '1' while the old data has status '0'?

Upvotes: 0

Views: 58

Answers (2)

ysth
ysth

Reputation: 98508

It sounds like you want, after your insert, for home_id/home_name values added to Home_History to have status 1 and values that weren't able to be added to have status 0. To do this you need a unique key on (home_id, home_name), not (home_id, status).

Upvotes: 1

Simone Rossaini
Simone Rossaini

Reputation: 8162

For execute two query you cant use "AND" but use this code :

$sql = "
INSERT INTO Home_History (home_id, home_name) SELECT home_id, home_name FROM 
Home_Temp ON DUPLICATE KEY UPDATE status = '0'; 
INSERT INTO Home_History (home_id, home_name) SELECT home_id, home_name FROM 
Home_Temp; 
";

try {
     $stmt = $db->prepare($sql);
     $stmt->execute();
    }

Upvotes: 0

Related Questions