Update on Duplicate key with two columns to check mysql

Question

I am trying to get my head around the 'On Duplicate Key' mysql statement. I have the following table: id (primary key autoincr) / server id (INT) / member id (INT UNIQUE KEY) / basket (VARCHAR) / shop (VARCHAR UNIQUE KEY)

In this table each member can have two rows, one for each of the shops (shopA and shopB). I want to INSERT if there is no match for both the member id and shop. If there is a match I want it to update the basket to concat the current basket with additional information.

I am trying to use: "INSERT INTO table_name (server_id, member_id, basket, shop) VALUES (%s, %s, %s, %s) ON DUPLICATE KEY UPDATE basket = CONCAT (basket,%s)"

Currently if there is an entry for the member for shopA when this runs with basket for shopB it adds the basket info to the shopA row instead of creating a new one.

Hope all this makes sense! Thanks in advance!

UPDATE: As requested here is the create table sql statement: CREATE TABLE table_name ( member_id bigint(20) NOT NULL, server_id bigint(11) NOT NULL, basket varchar(10000) NOT NULL, shop varchar(30) NOT NULL, notes varchar(1000) DEFAULT NULL, PRIMARY KEY (member_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

As you explained the requirement neither member_id nor shop should be unique. Both of them combined should be unique. Post the CREATE TABLE statement of the table. — forpas, May 03 '22 at 15:27
CREATE TABLE `table_name` ( `member_id` bigint(20) NOT NULL, `server_id` bigint(11) NOT NULL, `basket` varchar(10000) NOT NULL, `shop` varchar(30) NOT NULL, `notes` varchar(1000) DEFAULT NULL, PRIMARY KEY (`member_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 — John Kane, May 03 '22 at 22:20

score 1 · Accepted Answer · answered May 04 '22 at 12:16

In this table each member can have two rows, one for each of the shops (shopA and shopB)

This means that member_id should not be the primary key of the table because it is not unique.
You need a composite primary key for the columns member_id and shop:

CREATE TABLE table_name ( 
  member_id bigint(20) NOT NULL, 
  server_id bigint(11) NOT NULL, 
  basket varchar(10000) NOT NULL, 
  shop varchar(30) NOT NULL, 
  notes varchar(1000) DEFAULT NULL, 
  PRIMARY KEY (member_id, shop) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

See a simplified demo.

Update on Duplicate key with two columns to check mysql

1 Answers1