How to avoid duplicate data into Mysql table?

Question

order-:

order_id(PK)    item     status  order_no
1               pant        0       100
2               shirt       1       200

product-:

   prod_id(Fk)    id(pk)   price     quantity   order_no  status
   1               1         10          2          100        0
   1               2         20          3          100        0   
   2               3         15          1          200        1

These are my two table-: order & product.

what happend is to insert data into product table, my query first check status of order table & if it is 0, it add data into product table.

My program runs through CRON job..so each time it just checks order status & if it is 0, it again adds same data into product table.

i.e. my product table become like this

 prod_id(Fk)    id(pk)   price     quantity   order_no  status
   1               1         10          2          100        0
   1               2         20          3          100        0   
   2               3         15          1          200        1
   1               4         10          2          100        0
   1               5         20          3          100        0

which I don't want. my id column is pk which is auto-incremented... so how should I avoid duplicate data into Product table?

Is there any way we can update or replace the same data??

plz guide me..

eggyal · Accepted Answer · 2012-05-09 17:55:31Z

3

Create a UNIQUE index on prod_id (or whatever columns determine that the record is to be updated rather than inserted), and then use either INSERT ... ON DUPLICATE KEY UPDATE or REPLACE.

answered May 9, 2012 at 17:55

eggyal

127k18 gold badges220 silver badges244 bronze badges

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

4 Comments

Prat Over a year ago

HI eggyal, I can't create unique index on product table as I am maintaining foreign key prod_id which represent that these products are from that order no. so it may contain same value as an example u can check for order no. 1 there were 2 products.

eggyal Over a year ago

@Prat: how do you determine whether you want to insert a new record or update an existing one? Is it by the order_no? The combination of order_no and prod_id? Your question is unclear.

Prat Over a year ago

Order status is 0 means data is not moved into oracle...so when order_status become 1 means data move into oracle...but what happened is till the time data move, product data get inserted into product table which I dont want..

eggyal Over a year ago

Oracle? Your question is tagged MySQL. Anyway, you will need to edit your question to show your query. It sounds like you simply need to add a WHERE status = 1 clause somewhere...

Collectives™ on Stack Overflow

How to avoid duplicate data into Mysql table?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related