getting my Update query to create a new record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Kaervek
Forum Commoner
Posts: 25
Joined: Fri Jul 08, 2005 7:17 am
Location: Newfoundland
Contact:

getting my Update query to create a new record

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post by bdlang »

Can you write out a pseudo-SQL example?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Run a SELECT query first to test for existence.
Kaervek
Forum Commoner
Posts: 25
Joined: Fri Jul 08, 2005 7:17 am
Location: Newfoundland
Contact:

Post 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.
bdlang
Forum Contributor
Posts: 395
Joined: Tue May 16, 2006 8:46 pm
Location: Ventura, CA US

Post 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.
Kaervek
Forum Commoner
Posts: 25
Joined: Fri Jul 08, 2005 7:17 am
Location: Newfoundland
Contact:

Post 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
Post Reply