Reputation: 198
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
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
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