Jump to content
Sign in to follow this  
NotionCommotion

INSERT ON DUPLICATE KEY UPDATE with Composite key

Recommended Posts

Why does the INSERT ON DUPLICATE KEY UPDATE fail?  I read that I also need to add a UNIQUE INDEX (but I have no idea why) and did so, but it still fails.  Thanks

mysql> EXPLAIN points;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int(11)     | NO   | PRI | NULL    |       |
| accounts_id | int(11)     | NO   | PRI | NULL    |       |
| name        | varchar(45) | NO   | MUL | NULL    |       |
| value       | varchar(45) | YES  |     | NULL    |       |
| value_old   | varchar(45) | YES  |     | NULL    |       |
| units       | varchar(45) | YES  |     | NULL    |       |
| timestamp   | int(11)     | YES  |     | NULL    |       |
| type        | char(     | NO   | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> SHOW INDEX FROM points;
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name                   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| points |          0 | PRIMARY                    |            1 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | PRIMARY                    |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | name_UNIQUE                |            1 | name        | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | name_UNIQUE                |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points_pk           |            1 | accounts_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points_pk           |            2 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points2_pk          |            1 | id          | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          0 | unique_points2_pk          |            2 | accounts_id | A         |          27 |     NULL | NULL   |      | BTREE      |         |               |
| points |          1 | fk_points_accounts_id_idx  |            1 | accounts_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| points |          1 | fk_points_point_types1_idx |            1 | type        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
10 rows in set (0.00 sec)

mysql> SELECT * FROM points WHERE id=150;
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
| id  | accounts_id | name             | value  | value_old | units | timestamp | type |
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
| 150 |          51 | sample test one  | 102.67 | 102.74    | W     |      NULL | real |
+-----+-------------+------------------+--------+-----------+-------+-----------+------+
1 row in set (0.00 sec)

mysql> INSERT INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W') ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=NOW();
ERROR 1364 (HY000): Field 'name' doesn't have a default value
mysql>

 

 

Share this post


Link to post
Share on other sites

You have name set to not allow nulls and there is no default value set for that column. In your query you do nothing with the name column. As you have it, you can think of  'name' as a required field.

Edited by benanamen

Share this post


Link to post
Share on other sites

Because your statement is basically an INSERT to start with you have to satisfy all the conditions that a normal insert would have. That includes providing a value for all the NOT NULL columns that do not have a default.

 

The ON DUPLICATE KEY clause is basically just a way to provide some automatic error handling. Think of it as mysql attempting the insert but if it returns an error indicating a duplicate key violation then it re-issues an UPDATE query instead.

Share this post


Link to post
Share on other sites

Yes, but that is irrelevant. Mysql is going to error on the "try to insert" part of the query due to the missing column and never actually get to the update part. In pseudo-code it'd be something like:

if ($query->isValidInsert()){
   if (!$query->insert() && $query->getError() == 'duplicate'){
       $query->update();
   }
}
It's failing on the isValidInsert part because it's missing data.

Share this post


Link to post
Share on other sites

Dah...

 

I see several workarounds.

 

  1. Get rid of the NOT NULL constraint (which I don't wish to do).
  2. Get fancy with triggers or stored procedures (which I also don't with to do).
  3. Maybe the following?
INSERT INTO points (id, accounts_id, value, units, name, type) VALUES (150,51,158.6,'W','bogus','bogus')
ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP();

 

     4.  EDIT.  Or probably this.  An extra advantage is INSERTS are never really desired as I am doing this just as a multiple update hack.

 

INSERT IGNORE INTO points (id, accounts_id, value, units) VALUES (150,51,158.6,'W')
ON DUPLICATE KEY UPDATE value_old=value, value = VALUES(value), units = VALUES(units), timestamp=UNIX_TIMESTAMP();
Edited by NotionCommotion

Share this post


Link to post
Share on other sites

Ah, Benanamen with his aversion to XY questions :)

 

I am trying to efficiently perform mass update queries where some of the NOT NULL columns are not updated.  See https://forums.phpfreaks.com/topic/302727-multiple-updates/ for background history.

 

Thanks

 

PS.  As indicated by others responding to this post, a composite key has nothing to do with it.

Share this post


Link to post
Share on other sites

You could go back to just using an UPDATE statement rather than an INSERT hack. I posted another alternative method in your original thread.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.