What is the fastest approach to update a row and if the parameters don't exist insert it.
My table has 2 columns for ids quote_id, order_id and those columns combined would make a unique column. I don't want 2 rows containing the same quote_id and order_id but either can have multiple rows.
id | quote_id | order_id
1 | q200 | o100
2 | q200 | o101
3 | q201 | o100
Previously I would have added a third field and combined those 2 fields with a - so I could use the ON DUPLICATE KEY UPDATE. But this is not very efficient as I seem to forget to add those fields sometimes.
My idea is to try to run the update query and if it fails the insert it as I run a lot more update queries then insert. How would I put this into a single query instead of the MySQL server having to return a error and then I rerun the insert query.
if (
UPDATE table_name SET column1=value, column2=value2 WHERE some_column=some_value === ERROR
) THEN
INSERT INTO table_name ....
I looked through some of the MySQL documentation and I couldn't find a example that showed how an error is detected in a IF statement
You should have a PRIMARY or UNIQUE constraint over the column(s) that identify rows uniquely. It's normal to use multiple columns for this, and SQL support syntax for it:
CREATE TABLE MyTable (
quote_id VARCHAR(4) NOT NULL,
order_id VARCHAR(4) NOT NULL,
other_data VARCHAR(4),
...
PRIMARY KEY(quote_id, order_id)
);
Then you can rely on the unique constraint to cause an INSERT to fail and run an UPDATE instead:
INSERT INTO MyTable (quote_id, order_id, other_data) VALUES ('q200', 'o100', 'blah blah')
ON DUPLICATE KEY UPDATE
other_data = VALUES(other_data);
Using the VALUES() clause in the UPDATE part means "use the same value for the respective column that I tried to use in the INSERT part."