CODEWITHSUNDEEP
mysql
Roi patrick Florentino
Roi patrick Florentino

Reputation: 177

Mysql script update with if statement and case

Why Can't I use or why does it encounters an error. I'm trying to use a script as such as the one below. I'm a newbie when it comes to mysql and I tried creating scripts like this but I get the errr like the below

#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 '1 THEN dogs.dogcount = dogcount - dogdetails.total, dogdetails.statu' at line 4

Here's my script

UPDATE dogs 
LEFT JOIN dogdetails ON dogs.username = dogdetails.username 
SET dogs.dogcount = 
CASE WHEN dogdetails.dogcount IS NOT 1 THEN dogs.dogcount = dogcount -    dogdetails.total, dogdetails.status = 'Checked'
WHEN dogdetails.dogcount = 1 THEN dogs.pto = pto - dogdetails.total, dogdetails.status = 'Checked'
WHERE dogdetails.id=4

Is there somethng I'm missing or overlooked?

Upvotes: 0

Views: 82

Answers (1)

neiha
neiha

Reputation: 171

remember that you have to use the keyword 'end' after your case statements and to always return a value. The IS NOT keyword is also incorrect when comparing numbers. Here's a version of your query that should work:

UPDATE dogs 
LEFT JOIN dogdetails ON dogs.username = dogdetails.username 
SET dogs.dogcount = CASE WHEN dogdetails.dogcount != 1 THEN dogs.dogcount = dogcount-dogdetails.total else dogs.dogcount end, 
dogs.pto= case WHEN dogdetails.dogcount = 1 THEN  pto - dogdetails.total else pto end,     
dogdetails.status = 'Checked'
WHERE dogdetails.id=4

Upvotes: 1

Related Questions