Jester
Jester

Reputation: 198

MariaDB INSERT INTO... SELECT... ON DUPLICATE KEY UPDATE affecting 0 rows

I've created the following table in MariaDB

Table creation

CREATE TABLE `email_templates_pending` (
  `template_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `template_name` varchar(100) NOT NULL,
  `template_data` text,
  `modify_type` varchar(16) NOT NULL,
  `modify_by` varchar(50) NOT NULL,
  `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`template_id`),
  UNIQUE KEY `template_name` (`template_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

And inserted a row in that table with the template_id = 1. I wanted to update the row using the following INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE statement below

SQL Statement

INSERT INTO email_templates_pending
          (template_id, template_name, template_data,  modify_by,    modify_type) 
    SELECT template_id, template_name, template_data, '[email protected]', 'Deleted'
        FROM email_templates WHERE template_id= '1' 
ON DUPLICATE KEY UPDATE modify_type='Deleted'

However, when I run the statement it returns successful but with 0 rows affected. I have another similar table with different column names that works as intended. I've ensured that the template_id is the primary key, so I'm not sure what else the issue could be?

Upvotes: 2

Views: 1572

Answers (2)

Rick James
Rick James

Reputation: 142298

If id#1 existed and was already marked as "Deleted", you would correctly get "0 rows affected".

To prove that this is not the case, let's see the output from

SELECT template_id, template_name, template_data,  modify_by,    modify_type
    FROM email_templates WHERE template_id= '1' ;

Upvotes: 0

Dylan Su
Dylan Su

Reputation: 6065

You have 1 row in email_templates_pending, but no rows in email_templates.

That may be the reason why 0 row affected. No row is in the source table.

INSERT INTO email_templates_pending ...
SELECT ... FROM email_templates

If you just want to update for id = 1, you can use this:

INSERT INTO email_templates_pending (template_id, template_name, template_data, modify_by, modify_type) 
SELECT template_id, template_name, template_data, '[email protected]', 'Deleted' FROM email_templates_pending 
ON DUPLICATE KEY UPDATE modify_type='Deleted';

If you just need to do UPDATE, may be a direct UPDATE statement can also be used.

UPDATE email_templates_pending SET modify_type='Deleted' WHERE template_id= '1';

Upvotes: 1

Related Questions