0

I'm using mysql database. I'm having a main table with id as primary key with (auto increment,not null, unique) constraints.

My main table :

sample of my table

In secondary table i have the same fields but without id column

enter image description here

Now i need to update the main table from the secondary table with the following condition.

if a user data exists in main table, it should be replaced with secondary table data and new rows should add. (example: 1st row of user1 needs to be replaced with secondary table data , and new rows of user1 should be added in the main table)

Expected output:

enter image description here

I have tried with insert...on duplicate update. it doesn't worked ! Any help?

Jones Kumar
  • 67
  • 6

1 Answers1

-1

Try the following steps.

Step 1: As you want to replace the data in main table if the user exists in the second table, first delete all the data from main table which exists in the second table

delete from main_table where login_name in (select login_name from second_table);

Step 2:

insert into main_table (login_name, mgr_name, ...)
select login_name, mgr_name,... from second_table

Here in the first step, you are deleting users which are present in the second table and in step 2, you will insert all users from second_table hence you will eventually have your existing users replaced in the main_table.

Mayank Patel
  • 1,563
  • 1
  • 14
  • 19
  • Appreciate you answer! But in your solution primary key value is going to change for all those record which will update values in main table. – Meet Patel Jan 23 '20 at 10:20
  • Yeah, I thought about it. So from the second table, if 3 records found for user1, which record will you replace in the main table? Also, you want to achieve it in python or pure SQL? – Mayank Patel Jan 23 '20 at 10:25
  • looking at screen shot @Jones Kumar wants 1st to be replaced out of 3 – Meet Patel Jan 23 '20 at 10:30
  • Yeah, I know. So there are 3 records for user1 so how would you identify that he want to replace it with 1st, 2nd or 3rd row? – Mayank Patel Jan 23 '20 at 10:41
  • i dont want the records to be replaced strictly, old data should delete and new data should be there. – Jones Kumar Jan 23 '20 at 11:00