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?

score 1 · Answer 1 · answered Oct 08 '12 at 20:20

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 08 '12 at 20:20

GolezTrol

114,394
18
182
210

+1 for pointing out foreign-key considerations, that could certainly screw up my suggestion. (i.e. delete then insert) – Richard Close Oct 08 '12 at 20:22
It is not auto increment. The products have unique id's that I am inserting already. May replace into? – Tyler Nichol Oct 08 '12 at 20:39
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. – GolezTrol Oct 08 '12 at 20:43
But if I just do an update will products not yet in the database be skipped over? – Tyler Nichol Oct 08 '12 at 20:50
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. http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html – GolezTrol Oct 09 '12 at 09:43

score 0 · Answer 2 · answered Oct 08 '12 at 20:43

0

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

answered Oct 08 '12 at 20:43

DocJones

649
7
26

Drop table in sql and add

2 Answers2