Insert or update (with no keys)
I have a table which has two fields. There can be multiple rows with the same value of each field, but only one row where both values are the same.
Hmm, that didn't sound very good, so here's some examples:
good stuff
a, 1
a, 2
b, 1
b, 2 < ==== all are unique combinations
bad stuff
a, 1
a, 1 <=== this permutation should only occur once
So, as pseudo-code: if no row exists with both columns of the give values, then UPDATE INSERT a new row (else, do nothing).
The table has no keys (is that wrong?). Is there a single command to do this, sort of INSERT IF NOT EXISTS
? Or do I first have to search, then decide whether to INSERT?
ODBC solution preferred, although it is currently only MySql
You may be looking for INSERT ... ON DUPLICATE KEY UPDATE
. (Note that you must have a unique key in your table for this to work)
- I don't think you're looking for an
upsert
query - it would update if the key is found, otherwise it would insert. - You can make more than one column a primary key. Doing this sets a unique constraint on the table, which won't allow for duplicate inserts. In most databases, though, it will kick back an error, so if you're doing a batch update/copy, then none of the updates will be committed.
In database terminology making multiple fields into a primary key is referred to as a composite/compound/concatenated key.
精彩评论