Insert and update in mysql

Question

Here i have to update the table as follows,

My table looks like,

| subject | Type | Gender | s1 | s2 | s3 | s4 | s5 | s6 |
| sub1    | 1    |  M     | 10 | 20 | 0  | 0  | 0  | 0  |
| sub1    | 1    |  F     | 11 | 22 | 0  | 0  | 0  | 0  |
| sub1    | 2    |  M     | 30 | 40 | 0  | 0  | 0  | 0  |
| sub2    | 1    |  M     | 50 | 60 | 0  | 0  | 0  | 0  |
    .
    .
    .
| subn    | 2    |  F     | 500 | 600 | 0  | 0  | 0  | 0  |

here i have another table 2,

| subject | Type | Gender | s1| s2| s3 | s4  | s5 | s6 |
| sub1    | 1    |  M     | 0 | 0 | 11 | 16  | 25 | 30 |
| sub1    | 1    |  F     | 0 | 0 | 12 | 17  | 24 | 29 |
| sub1    | 2    |  M     | 0 | 0 | 13 | 18  | 23 | 28 |
| sub1    | 2    |  F     | 0 | 0 | 14 | 19  | 22 | 27 |
| sub2    | 1    |  M     | 0 | 0 | 15 | 20  | 21 | 26 |
   .
    .
    .
| subn    | 2    |  F     | 0 | 0 | 50  | 60 | 70 | 80 |

I need to update the values in table 1 from table 2. Don't look the values it's sample. Note: Extra rows may be less or more in both tables. If the row is not avail in table 1, have to identify and insert that row too in table 1 from table 2. If you need any queries regarding this i am here. thanks in advance.

Answer 1

For the update:

UPDATE table1
JOIN table2 ON table1.subject = table2.subject AND 
               table1.type = table2.type AND 
               table1.gender = table2.gender
SET table1.s1 = table2.s1, table1.s2 = table2.s2, 
    table1.s3 = table2.s3, table1.s4 = table2.s4, 
    table1.s5 = table2.s5, table1.s6= table2.s6

For inserting rows that does not exist in table1:

INSERT INTO table1
SELECT *
FROM table2
WHERE (subject, type, gender) NOT IN (
   SELECT subject, type, gender
   FROM table1
)

Answer 2

You want to use insert . . . on duplicate key update. First, you need a unique key to define duplicates. Let me assume it is the first three columns:

create unique index idx_table_subject_type_gender on table(subject, type, gender);

Then you can do what you want as:

insert into table1(subject, type, gender, s1, s2, s3, s4, s5, s6)
    select subject, type, gender, s1, s2, s3, s4, s5, s6
    from table2 t2
    on duplicate key update s1 = t2.s1, s2 = t2.s2, s3 = t2.s3, s4 = t2.s4, s5 = t2.s5, s6 = t2.s6;