How to update row data then insert the latest data on mysql

Question

I have 'Home' table to save all data from importing csv file to phpMyAdmin. At first, the table not containing any data and i import a csv file that contain field (home_id, name and count). The table have additional field 'status' default to 1 and home_id is set as unique key. Now the table 'Home' have data.

home_id | name | count | status

H1 | Apartment | 3 | 1

H2 | Condominium | 6 | 1

H3 | Cottage | 4 | 1

Next, i import same csv file but with updated value for 'count'. By using INSERT INTO....DUPLICATE KEY UPDATE, i manage to update row that have same home_id and set 'status' as 0.

home_id | name | count | status

H1 | Apartment | 8 | 0

H2 | Condominium | 9 | 0

H3 | Cottage | 4 | 1

But i want to save data before and after updated for example as below table.

home_id | name | count | status

H1 | Apartment | 3 | 1

H2 | Condominium | 6 | 1

H1 | Apartment | 8 | 0

H2 | Condominium | 9 | 0

H3 | Cottage | 4 | 1

I realize that i cannot set 'home_id' as unique key because it will not allowed to save same 'home_id'. Is there any way i can do? I still cannot figure out on this.

Additional: Before i INSERT INTO...ON DUPLICATE KEY UPDATE to 'Home' table. I LOAD DATA INFILE to a temp table, 'Home_Temp' consist field (home_id, name, and count). Then i INSERT INTO to 'Home' table consist field (home_id, name, count and status). The 'Home_Temp' table is created when user click button for import file and drop after a insert process to 'Home' table.

After you've loaded the new data into Home_Temp, update it based on the latest values for each home_id in Home. Then write those updated values to Home — Nick
– Nick, Commented Dec 2, 2019 at 2:59
I'm sorry, i cannot understand on update based on latest values for home_id. @Nick — Malca
– Malca, Commented Dec 2, 2019 at 3:06
you would need an auto-incrementing id column or similar so then the "latest" value for a given home_id would be the one with the highest id value. — Nick
– Nick, Commented Dec 2, 2019 at 3:07
It is mean i have to do the update latest value on Home_Temp?. Because Home_Temp table is created when user click button for import file and drop after a insert process to Home table. @Nick — Malca
– Malca, Commented Dec 2, 2019 at 3:14
Something like LOAD DATA INFILE INTO TABLE Home_Temp; UPDATE Home_Temp SET status = 0, count = count + (SELECT count FROM Home WHERE home.home_id = Home_Temp.id AND id = (SELECT MAX(id) FROM Home h2 WHERE h2.home_id = Home.home_id)); INSERT INTO Home SELECT * FROM Home_Temp; — Nick
– Nick, Commented Dec 2, 2019 at 3:26

Malca · Accepted Answer · 2019-12-03 09:49:08Z

0

With help from @Nick, i find idea to solve this problem. I add field 'id' that auto increment. This query will find the highest home_id based on id and will update the status to '0'.

$sql = "UPDATE Home h1 INNER JOIN (
        SELECT home_id, MAX(id) AS max_id 
        FROM Home GROUP BY home_id) h2 
        ON h1.home_id = h2.home_id 
        AND h1.id = h2.max_id 
        SET h1.status = '0'";

answered Dec 3, 2019 at 9:49

Malca

1232 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update row data then insert the latest data on mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related