How to save delete result to variable in mysql?

Question

I've tried to use this, but it throws syntax error.

SET @deletedRepliesCount = (DELETE FROM reply WHERE type = 3 AND id IN (SELECT id FROM `like`
WHERE commentId = :commentId)) + @deletedRepliesCount;

"com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELETE FROM reply WHERE type = 3 AND id IN (SELECT id FROM like WHERE comment' at line 1"

That would be correct. The `delete` statement does not return a value. You want to use `ROW_COUNT()`. — Gordon Linoff, Mar 03 '15 at 14:42

score 1 · Accepted Answer · answered Mar 03 '15 at 14:43

1

Try this:

DELETE FROM reply WHERE type = 3 AND id IN (SELECT id FROM `like`
WHERE commentId = :commentId));
SET @deletedRepliesCount = SELECT ROW_COUNT() + @deletedRepliesCount

answered Mar 03 '15 at 14:43

David P

2,027
3
15
27

score 0 · Answer 2 · edited May 23 '17 at 12:20

0

Try below as per link

SET @deletedRepliesCount := (DELETE FROM reply WHERE type = 3 AND id IN (SELECT id FROM like WHERE commentId = :commentId)) + @deletedRepliesCount;

edited May 23 '17 at 12:20

Community

1
1

answered Mar 03 '15 at 14:44

Rao

20,781
11
57
77

score 0 · Answer 3 · answered Mar 03 '15 at 14:57

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,created DATETIME NOT NULL
,modified TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
,deleted TINYINT NOT NULL DEFAULT 0
);

INSERT INTO my_table (created) VALUES 
('2015-01-01 00:00:00'),
('2015-02-01 00:00:00'),
('2015-03-01 00:00:00'),
('2015-03-02 00:00:00'),
('2015-03-02 00:00:00'),
('2015-03-03 00:00:00');

UPDATE my_table SET deleted = 1 WHERE id > 3;

SELECT * FROM my_table WHERE deleted = 1;
+----+---------------------+---------------------+---------+
| id | created             | modified            | deleted |
+----+---------------------+---------------------+---------+
|  4 | 2015-03-02 00:00:00 | 2015-03-03 14:56:11 |       1 |
|  5 | 2015-03-02 00:00:00 | 2015-03-03 14:56:11 |       1 |
|  6 | 2015-03-03 00:00:00 | 2015-03-03 14:56:11 |       1 |
+----+---------------------+---------------------+---------+

How to save delete result to variable in mysql?

3 Answers3