CODEWITHSUNDEEP
mysqlsql
rfnpinto
rfnpinto

Reputation: 11

Update MySQL tables with some duplicated entries

I have two tables:

Table 1

Column 1 |  Column 2 |  Column 3
---------------------------------
1        |  a        |  10
         |           |
3        |  g        |  30
         |           |
5        |  f        |  50
         |           |
7        |  f        |  70

Table 2

Column 1 |  Column 2 |  Column 3
---------------------------------
10       |  bb       |  30

50       |  cc       |  40

Now, I need to copy the rows from table 2 to table 1.

The problem is:

If any entry in column 3 already exists in table 1. Column 3, then column 1 and column 2 from table 1 are updated with data from table 2. Rows that don't exist are just added in the end of the table.

Upvotes: 1

Views: 60

Answers (2)

Linger
Linger

Reputation: 15058

To update Column1 and Column2 if a record exists for Column3 else add new record, use:

CREATE UNIQUE INDEX Table1Column3 on Table1(Column3);

INSERT INTO Table1(Column1, Column2, Column3)
SELECT Column1, Column2, Column3
FROM Table2
ON DUPLICATE KEY UPDATE Column1 = Table2.Column1, Column2 = Table2.Column2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270421

Create a unique index in table 1 on column1 and column2:

create unique index table1_column1_column2 on table1(column1, column2);

Then use insert on duplicate key update:

insert into table1(column1, column2, column3)
    select column1, column2, column3
    from table2
    on duplicate key update column3 = table2.column3;

Upvotes: 2

Related Questions