Drop table in sql and add

Question

I have some information that I am downloading via API into a SQL database. I am setting up cron to do this in the middle of the night. Sometimes new products are added or old ones are edited. I need to add new ones and update old ones if they exist. I am pretty sure it looks something like:

If (id exists){
    update product
}else{
    insert product

Is this the best way? What about just dropping then re-constructing it?

GolezTrol · Accepted Answer · 2012-10-08 20:20:40Z

1

I would update the existing product, especially if the ID is an auto-number. But even if you have a surrogate key other than this ID, I'd still update existing products. In the future, your database may grow more complex and your products may get a couple of child tables. You don't want to reconstruct all of them.

Just update them.

answered Oct 8, 2012 at 20:20

GolezTrol

116k19 gold badges185 silver badges215 bronze badges

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

5 Comments

Richard Close Over a year ago

+1 for pointing out foreign-key considerations, that could certainly screw up my suggestion. (i.e. delete then insert)

Tyler Nichol Over a year ago

It is not auto increment. The products have unique id's that I am inserting already. May replace into?

GolezTrol Over a year ago

In that case, you could delete and insert, but there's still the problem of future child rows. I would choose to update the existing products anytime.

Tyler Nichol Over a year ago

But if I just do an update will products not yet in the database be skipped over?

GolezTrol Over a year ago

I mean to just update the records that do exist, instead of deleting and re-inserting them. Records that don't exist must be inserted, of course, so what you propose in general is right. Of course, specific databases have specific solutions for this, so now the MySQL tag is added to your questions, you could also investigate ON DUPLICATE UPDATE, as proposed by DocJones. dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

DocJones · Accepted Answer · 2012-10-08 20:43:02Z

0

You are looking for INSERT ... ON DUPLICATE UPDATE, i guess. See here.

answered Oct 8, 2012 at 20:43

DocJones

6771 gold badge8 silver badges26 bronze badges

Collectives™ on Stack Overflow

Drop table in sql and add

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related