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)

---

1. I don't think you're looking for an upsert query - it would update if the key is found, otherwise it would insert.
2. 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.