Page 1 of 1

getting my Update query to create a new record

Posted: Fri Jun 08, 2007 10:01 pm
by Kaervek
i wish to run a series of update query's, but if one of them has a where clause that returns no matching records i would like the query to create a new record for that case. Is this possible at all? if so could you suggest syntax

Thanks.

Posted: Fri Jun 08, 2007 10:11 pm
by bdlang
Can you write out a pseudo-SQL example?

Posted: Fri Jun 08, 2007 10:11 pm
by superdezign
Run a SELECT query first to test for existence.

Posted: Fri Jun 08, 2007 10:23 pm
by Kaervek
i suppose i could write a php script to first query for the result and if not found then create it and then run my original update query.
however i was hoping to have all these update querys saved into an .sql file which i could just run. In which case i would need the sql engine to do the work for me, not php.
If this is not possible i'll do it in php.

heres the Pseudo SQL... lol

UPDATE products SET price = 1.23 WHERE prod_id = 1234567
BUT IF prod_id DOESNT EXIST INSERT INTO products (prod_id, price) VALUES (1234567, 1.23);

sorta like that?

Thanks again.

Posted: Fri Jun 08, 2007 10:47 pm
by bdlang
If you're working with MySQL, take a look at REPLACE.

Code: Select all

REPLACE INTO products SET prod_id= 1234567, price= 1.23;
The `prod_id` field must be a PRIMARY KEY or UNIQUE.

Will not work with an AUTO_INCREMENT field, but then again, the query you're asking for assumes this.

Don't forget to update all fields in the table at the same time, as REPLACE will delete the record first, then UPDATE/INSERT.

Posted: Sun Jun 10, 2007 1:38 pm
by Kaervek
did some reading from that link you posted

I think
INSERT ... ON DUPLICATE KEY UPDATE
http://dev.mysql.com/doc/refman/5.0/en/ ... icate.html
is more along the lines of what i want

but i dont understand what all this c=c+1 is about.

can't i just do:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE