Update MySQL Column from a list-CodePudding

I'm trying to change the Transaction date from this table:

CREATE TABLE `orderslip_transaction` (
  `id` bigint(20) NOT NULL,
  `transaction_number` varchar(20) NOT NULL,
  `transaction_date` datetime(6) NOT NULL,
  `transaction_is_active` tinyint(1) NOT NULL,
  `store_id` bigint(20) NOT NULL,
  `created_on` datetime(6) NOT NULL,
  `updated_on` datetime(6) NOT NULL,
  `user_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Indexes for table `orderslip_transaction`
--
ALTER TABLE `orderslip_transaction`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `transaction_number` (`transaction_number`),
  ADD KEY `orderslip_transaction_user_id_dd45478c_fk_auth_user_id` (`user_id`),
  ADD KEY `store_id-fk-store` (`store_id`);

And some data:

INSERT INTO `orderslip_transaction` (`id`, `transaction_number`, `transaction_date`, `transaction_is_active`, `store_id`, `created_on`, `updated_on`, `user_id`) VALUES
(243, '1540 | ALLCPB', '2022-12-01 03:23:00', 1, 1, '2022-12-10 11:13:14.026696', '2022-12-10 11:13:14.026696', 1), 
(244, '1541 | ALLCPB', '2022-12-01 04:00:29', 1, 1, '2022-12-10 11:11:26.442181', '2022-12-10 11:11:26.457804', 1), 
(247, '1545 | ALLCPB', '2022-12-01 07:19:08', 1, 1, '2022-12-10 11:09:10.739268', '2022-12-10 11:09:10.739268', 1), 
(248, '1546 | ALLCPB', '2022-12-01 10:14:45', 1, 1, '2022-12-10 11:19:31.026184', '2022-12-10 11:19:31.026184', 1), 
(262, '1577 | ALLCPB', '2022-12-01 08:36:15', 1, 1, '2022-12-10 12:09:06.744023', '2022-12-10 12:09:06.744023', 1), 
(271, '1583 | ALLCPB', '2022-12-01 13:30:31', 1, 1, '2022-12-10 12:02:36.909668', '2022-12-10 12:02:36.925290', 1);

(This is from the MySQL Export)

What I want to do is to change the transaction date from a csv list with this example data set:

('1545 | ALLCPB', '2022-12-10 07:19:08', 1, 1, '2022-12-10 11:09:10.739268', '2022-12-10 11:09:10.739268', 1), 
('1546 | ALLCPB', '2022-12-12 10:14:45', 1, 1, '2022-12-10 11:19:31.026184', '2022-12-10 11:19:31.026184', 1), 
('1577 | ALLCPB', '2022-12-23 08:36:15', 1, 1, '2022-12-10 12:09:34.693302', '2022-12-10 12:09:34.693302', 1),
('1583 | ALLCPB', '2022-12-24 13:30:31', 1, 1, '2022-12-10 12:02:36.909668', '2022-12-10 12:02:36.925296', 1);

I tried using

INSERT INTO orderslip_transaction (transaction_number, transaction_date, transaction_is_active, store_id, created_on, updated_on, user_id) VALUES
('1545 | ALLCPB', '2022-12-10 07:19:08', 1, 1, '2022-12-10 11:09:10.739268', '2022-12-10 11:09:10.739268', 1), 
('1546 | ALLCPB', '2022-12-12 10:14:45', 1, 1, '2022-12-10 11:19:31.026184', '2022-12-10 11:19:31.026184', 1), 
('1577 | ALLCPB', '2022-12-23 08:36:15', 1, 1, '2022-12-10 12:09:34.693302', '2022-12-10 12:09:34.693302', 1),
('1583 | ALLCPB', '2022-12-24 13:30:31', 1, 1, '2022-12-10 12:02:36.909668', '2022-12-10 12:02:36.925296', 1)
ON DUPLICATE KEY UPDATE orderslip_transaction.transaction_date = VALUES(transaction_date);

I purposefully omitted the id column in the INSERT... statement since I only would like to check for the transaction_number. Running the query does not result in an error, but the result when querying SELECT * FROM orderslip_transaction WHERE transaction_number = "1583 | ALLCPB"; stil gives me 2022-12-01 13:30:31.000000 as its transaction_date. I don't need to change the other columns, just the transaction_date column.

Further reading from https://mariadb.com/kb/en/insert-on-duplicate-key-update/, I saw that ON DUPLICATE KEY UPDATE () only works well when there is only 1 primary key, and that using ON DUPLICATE KEY UPDATE might not produce the desired results when there are more than 1 unique keys (in this case, id and transaction_number are both unique, with id being an auto-increment primary key)

Any help is highly appreciated. Thanks in advance!

I'm running XAMPP v3.3.0, MySQL v5 , btw.

CodePudding user response：

Running the query does not result in an error

This is extremely strange. You must receive an error similar to "Column id does not have default value". Maybe you hide it by some way? The fact that the data is not updated also confirms that the query fails.

While fixing this issue you query runs successfully. See fiddle.

But I recommend you not to set default value but to make id autoincremented.

Further reading from https://mariadb.com/kb/en/insert-on-duplicate-key-update/, I saw that ON DUPLICATE KEY UPDATE () only works well when there is only 1 primary key, and that using ON DUPLICATE KEY UPDATE might not produce the desired results when there are more than 1 unique keys (in this case, id and transaction_number are both unique, with id being an auto-increment primary key)

I think that this is not correct. ODKU fires when ANY unique index violation is detected. The article by the link tries to tell that if some unique key violation is found and ODKU is fired then some other unique key violation may occur which will result in the whole query fail (common double fault) - but the same can be produced on one unique key easily.. fiddle.