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
– Gordon Linoff, Commented Mar 3, 2015 at 14:42

David P · Accepted Answer · 2015-03-03 14:43:08Z

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 3, 2015 at 14:43

David P

2,1433 gold badges20 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:20:37Z

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, 2017 at 12:20

CommunityBot

11 silver badge

answered Mar 3, 2015 at 14:44

Rao

21.5k12 gold badges63 silver badges80 bronze badges

Comments

Strawberry · Accepted Answer · 2015-03-03 14:57:30Z

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 |
+----+---------------------+---------------------+---------+

Collectives™ on Stack Overflow

How to save delete result to variable in mysql?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related