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.
getting my Update query to create a new record
Moderator: General Moderators
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
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.
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.
If you're working with MySQL, take a look at REPLACE.
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.
Code: Select all
REPLACE INTO products SET prod_id= 1234567, price= 1.23;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.
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
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