ON DUPLICATE KEY UPDATE for a specific column?

In a few different database managers, using the INSERT ON DUPLICATE KEY UPDATE clause can be used to UPDATE a row instead of INSERT if the INSERT causes a duplicate in any UNIQUE column or PRIMARY KEY.

However, I was wondering, is it possible to achieve the same functionality but only for a specific column? That is, when UNIQUE column X tries to INSERT a duplicate, it gets updated instead. But when UNIQUE column Y tries to INSERT a duplicate, it still throws an error?

Example

If I have a table with two unique columns.
  UNIQUE               UNIQUE
    V                    V
+------+-------------+--------+
| row  | Description | Bay    |
+------+-------------+--------+
|    0 | Things      | 2      |
|    1 | KnickKnacks | 4      |
+------+-------------+--------+

And one of the columns (titled ‘row’ in this example) tries to INSERT a duplicate value, it UPDATEs the whole entry, but when the other column (‘Bay’ in this example) tries to INSERT a duplicate value, it fails and throws an error.

Is it possible to do that in one statement like you can with the regular clause?

    INSERT INTO tableName (row, Description, Bay)
    VALUES (rowNum, "desc", "bayNum")
    ON DUPLICATE KEY UPDATE Description = "desc", Bay = "bayNum";

How would I accomplish this? Thanks a lot!

It sounds like there is a problem somewhere. How about tell us about the overall problem/task you are trying to solve with this attempt. What is the big picture of what you have going on?

no, sorry

I have a table that keeps track of different attributes of a set of products. Each column is a different attribute, each row is a different item.

I use a column named ‘row’ as essentially an ‘id’ column but with some other specific functionality. When the user submits a change to the product table, if they try to update the info of an item that already has a row (i.e. already has that ‘row’ number in the table) then I want it to update that row with the new info.
If they try an update a different UNIQUE column with a duplicate value from another row, I want an error to be thrown.

The ‘row’ column is always guaranteed to be UNIQUE. It can’t be determined ahead of time which other rows will be UNIQUE (the user chooses that based on some other program functionality).

You have a database design problem. Post an SQL dump of your DB with a few sample records so we can help you out.

This is a small database and I’m actually designing it right now, so I don’t have any full records to share yet. What would you identify as the design problems based on the previous description?

P.S. the table names, column names, and if each column will/will not be UNIQUE is determined dynamically. It is all based on info the user enters into a form.

Learn about Database Normalization.

There should not be a column for every attribute.

Alright, thanks, I’ll take a look at it soon.
Apologies for not knowing about this before; I’m mostly self-taught in this area.

We all started at the beginning…

what alternative would you suggest?

because that sounds fine to me

example: an employee table, where each employee is one row, and there is a column for every employee attribute

how else would you do it?

I am surprised you in particular are asking that. Before I answer, what if an entity, whatever it may be has hundreds of “attributes”, either as a single entity or several different entities that have attributes that have nothing to do with another entity. Are you really advocating potentially endless columns to handle attributes.

A simple product example for shoes and shirts. They could share some attributes such as color, but they also can have unique attributes. Add in another product with unique attributes and now throw in even more columns?

subtype/supertype tables – product is the supertype, each subtype has its own attributes

maybe let OP determine whether this is appropriates? so far, we’ve seen only row, Description, and Bay

I agree. I asked OP in PM for his data info. Also agree with the other tables depending on what exactly is going on.

Well the thing is, I have a set of tables and each of them represents a supplier that my company buys from. Each of the tables contains some common items that my company orders from that supplier, and some different attributes you might want to edit about items you are ordering. Each row is an item, and each column is the different attributes you can edit about the item.

Now the reason I have each row as an item is because I am just creating the database as a useful resource for an “autofill” function on another page. We’re not uploading every item from all of the suppliers’ catalogs to our database. We’re just creating some default items that the employees (who are doing the ordering) can select from when placing an order to a supplier, instead of forcing them to manually re-type all the information for each item every time.

When they select an item that we have in our database (because it shows up in the autocomplete), I use the rest of the attributes in the database to autofill in the rest of the fields. So if they select “poster board” I autofill the “size” and “color” attributes based on the size/color we usually order, which is stored in our database.

That’s some really fast context for it, I’ll explain more and respond to @benanamen’s private message in a few hours when I have time.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.