Quick MySQL question
 

I'm trying to write out an SQL text file from my item editor which anyone can source into their database. The problem I've got is that MySQL seems to want me to use two different methods depending on whether the item already exists or not.

If the item doesn't exist I do this ..

INSERT INTO items VALUES (blah blah ..

If it already exists then I do this ..

UPDATE items SET raw_data = '%s' WHERE id = '%d'

Is there a way to combine these two into a 'catch-all' statement? I can write out different entries based on what exists and what doesn't in my database, but that's no use to any one else.

My best guess so far was to write out two lines for each item like this ..

INSERT IGNORE INTO items VALUES (%d,'%s');
UPDATE items SET raw_data = '%s' WHERE id = %d;

The first line will be ignored if the item exists and then the second line will update the existing item. If the item doesn't exist then it gets inserted and then updated again. This looks nasty, anyone know if there's a nicer way?

Thanks.
K.

What about adding the statement to truncate the table first? It removes the rows (data) and leaves the tabledef intact. At that point, they can all be inserts.

HTH

BoB

I was going to say use a delete before the insert to make it cleaner, but it turns out REPLACE is the command you really want. It is probably the right command to use a lot of other times too, like adding/updating items...


REPLACE INTO items VALUES (%d,'%s');

A little more verbose, but it is suggested to name the columns, just incase the table changes.

REPLACE INTO items (id,raw_data) VALUES (%d,'%s');

Thanks Lurker, that was exactly what I was looking for.

K.