If exists update +1 for each occurence

Question

I currently have, and i'm trying to display the first and last name when there is a bad experience linked to that id. And if a bad experience exists I want to add 1 to my column "strikes" I'm pretty stuck at this point.

SELECT first, last FROM staff JOIN comments 
ON staff.id = comments.staff_id AND exp = 'bad' 
AND IF EXISTS exp = 'bad'
UPDATE comments
SET strikes = +1
WHERE exp = 'Bad';

I get this error, but nothing I've done solves the issue,Error Code: 1064. 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 'EXISTS exp = 'bad' UPDATE comments SET strikes = +1 WHERE exp = 'Bad'' at line 3 — Jake, Oct 11 '17 at 23:02

score 0 · Answer 1 · answered Oct 11 '17 at 23:04

0

I don't understand your tables, but how about something like this:

UPDATE comments
SET strikes = strikes + 1
WHERE exp = 'Bad';

answered Oct 11 '17 at 23:04

WW.

23,793
13
94
121

score 0 · Answer 2 · answered Oct 11 '17 at 23:06

This is not how IF EXISTS is used. Take a look at this StackOverflow thread.

IF EXISTS looks at the result of a subquery to check whether that returns anything. So you should be changing your query to:

create procedure select_or_insert()
begin
   IF EXISTS (
              SELECT first, last 
              FROM staff JOIN comments 
              ON staff.id = comments.staff_id AND exp = 'bad'
   )
   UPDATE comments
   SET strikes = +1
   WHERE exp = 'Bad';
end $$

score 0 · Answer 3 · answered Oct 11 '17 at 23:08

As far as I understood, You have a table staff. When an id have exp as bad, you want to update strikes in comments table as no of bad experience for that id.

Update comments as c
   INNER JOIN (SELECT id, count(*) As badexp_cnt
                    FROM staff
                  where exp = 'bad'
                  group by id) s
    ON s.id = c.staff_id
SET strikes = badexp_cnt

-- Do you need strikes + badexp_cnt or just badexp_count? Strike + badexp_count will increase the strike value, whenever you update

If exists update +1 for each occurence

3 Answers3