CODEWITHSUNDEEP
mysqlsql
astroboy
astroboy

Reputation: 197

How to insert unique records from one table to another in MySQL

I've a sample table table1:

id     transaction_number     net_amount      category      type
1      100000                  2000            A            ZA
2      100001                  4000            A            ZA
3      100002                  6000            B            ZB

I've a sample table table2:

id     transaction_number      net_amount      category        type
1      100002                  6000               B             ZB

How do I insert unique records that are not in table2, but present in table1?

Desired result:

id    transaction_number       net_amount     category        type
1      100002                   6000              B           ZB
2      100000                   2000              A           ZA
3      100001                   4000              A           ZB

Upvotes: 1

Views: 824

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

If you don't want to duplicate transaction numbers in table2, then create a unique index or constraint on that column (or the columns you want to be unique). Let the database handle the integrity of the data:

alter table table2 add constraint unq_table2_transaction_number
    unique (transaction_number);

Then use on duplicate key update with a dummy update:

insert into table2 (transaction_number, net_amount, category, type)
    select transaction_number, net_amount, category, type
    from table1
    on duplicate key update transaction_number = values(transaction_number);

Why do I recommend this approach? First, it is thread-safe, so it works even when multiple queries are modifying the database at the same time. Second, it puts the database in charge of data integrity, so the transactions will be unique regardless of how they are changed.

Note that the most recent versions of MySQL have deprecated this syntax in favor of the (standard) on conflict clause. The functionality is similar, but I don't think those versions are widespread.

Upvotes: 1

Popeye
Popeye

Reputation: 35930

Usw not exists as follows:

Insert into table2
Select t1.*
  From table1 t1
 Where not exists 
       (Select 1 from table2 t2
         Where t1.transaction_number = t2.transaction_number)

Upvotes: 0

kmoser
kmoser

Reputation: 9308

INSERT INTO table2 ( transaction_number, net_amount, category, type )
(
  /* Rows in table1 that don't exist in table2: */
  SELECT ( table1.transaction_number, table1.net_amount, table1.category, table1.type )
  FROM table1
  LEFT JOIN table2 ON ( table1.transaction_number = table2.transaction_number )
  WHERE table2.transaction_number IS NULL
)

Upvotes: 2

Arun pandian M
Arun pandian M

Reputation: 882

Try this

INSERT INTO table2 (transaction_number,net_amount,category,type)
(SELECT transaction_number,net_amount,category,type from table1) ON DUPLICATE KEY UPDATE
net_amount=VALUES(net_amount),category=VALUES(category),type=VALUES(type);

Upvotes: 0

Related Questions