SQL create new row with values unless row with 2 columns match

Question

I create my table by doing so:

CREATE TABLE IF NOT EXISTS table_name (uuid VARCHAR(128), item VARCHAR(48), value FLOAT(11))

When I insert values, I want UUID and ITEM together to unique. By that I mean, if there is an entry where uuid and item matches the new values, it will update that one. If not, it will create a new one. So I don't want UUID to have a unqiue key, nor ITEM. But I sorta want them to become unique together. So there can't be 2 entries where both UUID and ITEM match the other entry's UUID and ITEM.

If my explanation wasn't awful, does anyone know how that could be done?

why cant you create a new column that concats 'uuid' and 'item' and make this new column unique?? — Nishanth Matha, Feb 21 '15 at 02:21
Because that seems really unintuitive and hacky. I expect there to be a better way. I also want to be able to query the contents of each specific column and such, and that won't really work if I merge them. — Vapid, Feb 21 '15 at 02:39

score 3 · Accepted Answer · edited May 23 '17 at 12:10

3

Is this what you're looking for? First make uuid and item your primary key.

CREATE TABLE IF NOT EXISTS table_name (uuid VARCHAR(128), item VARCHAR(48), value FLOAT(11), PRIMARY KEY(uuid, item)) ENGINE InnoDB;

Then use, "ON DUPLICATE UPDATE"

INSERT INTO table_name(UUID, item, value)
VALUES ('315c383c-b977-11e4-a6d1-954287e1d27a',  'item1', 1.0)
ON DUPLICATE KEY UPDATE value = VALUES(value);

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html Insert into a MySQL table or update if exists

edited May 23 '17 at 12:10

Community

1
1

answered Feb 21 '15 at 03:19

Voidmain

141
3

Yes, that looks perfect! I will try at asap and mark this as answered if it works! Can you explain why you explicitly specify InnoDB, is that a requirement? – Vapid Feb 21 '15 at 16:37
1

It's not a requirement. – Voidmain Feb 21 '15 at 18:10

score 0 · Answer 2 · answered Feb 21 '15 at 03:02

Try something like this.

To insert the rows only if it is a new combination

INSERT INTO table_name
SELECT uuid,
       item,
       value
FROM   source_table S
WHERE  NOT EXISTS (SELECT 1
                   FROM   table_name T
                   WHERE  S.uuid = T.uuid
                          AND s.item = t.item)

To update the rows if the combination already exists

UPDATE table_name T
       JOIN source_table S
         ON S.uuid = T.uuid
            AND s.item = t.item
SET    T.value = S.value

SQL create new row with values unless row with 2 columns match

2 Answers2