2 Replies - 219 Views - Last Post: 03 September 2017 - 10:05 PM

#1 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Issues with mysql CASE

Posted 03 September 2017 - 03:15 AM

Howdy y'all.

I'm trying to increase my knowledge of all of mysql's functions and such, and have come across a snag in one. Namely the "Case" function/method/thing. I've googled the heck out of this, trying to figure out the best way to update (tbl2) but only if (tbl1) doesn't meet some qualifier.

I appologize for the mess of code below.

UPDATE 
	user_coffers as UC, 
    user_upgrades AS UU,
	userscores as CC,
	map as MC,
	
SET 
	UC.balance = 
	CASE 
			WHEN UC.balance - CC.starsCapturedCurrent*10000 >= 0 
			THEN (UC.balance - ((CC.starsCapturedCurrent)*10000))
			ELSE
				(UPDATE map SET currentOwner = 0 WHERE currentOwner = UU.uid ORDER BY rand() LIMIT 1)
	END
	UC.energyCell = UC.energyCell+(FLOOR(UU.technology/2 *150)+100),
	UC.fuelCell = UC.fuelCell+(FLOOR(UU.technology/4 * 150)+100)
WHERE
	(UC.balance - ((CC.starsCapturedCurrent)*10000)) >= 0
	AND
	(UU.uid = UC.uid
	and
	CC.uid = UC.uid);

";


The main part i'm trying to fix, obviously is the
UC.balance = 
	CASE 
			WHEN UC.balance - CC.starsCapturedCurrent*10000 >= 0 
			THEN (UC.balance - ((CC.starsCapturedCurrent)*10000))
			ELSE
				(UPDATE map SET currentOwner = 0 WHERE currentOwner = UU.uid ORDER BY rand() LIMIT 1)
	END


Which i've been told by the lovely people over on stackoverflow (in a 3 year old post that has only one answer) that this should work, but I'm getting

Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SET UC.balance = (CASE WHEN UC.balance - CC.starsCapturedCurrent*100' at line 7 


I'm not 100% sure what i did wrong.

Is This A Good Question/Topic? 0
  • +

Replies To: Issues with mysql CASE

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13545
  • View blog
  • Posts: 54,057
  • Joined: 12-June 08

Re: Issues with mysql CASE

Posted 03 September 2017 - 07:41 AM

First off you need a comma after every set statement.. so line 14 needs to be reviewed.

Also.. logically line 18 in the WHERE would preclude any of case where the 'ELSE' would happen.. so you can most likely remove it all but line 8 and 11.
Was This Post Helpful? 0
  • +
  • -

#3 Lieoften  Icon User is offline

  • D.I.C Regular

Reputation: 21
  • View blog
  • Posts: 357
  • Joined: 06-January 10

Re: Issues with mysql CASE

Posted 03 September 2017 - 10:05 PM

By "Need a comma after every case" what exactly do you mean? or rather, where does it go? after the END or after the logic part of the case? (I'm assuming after the END. but like i said, i've been following tutorials on this and none of them fully make sense).

Also, I am an idiot for the where statement. oops.

This post has been edited by Lieoften: 03 September 2017 - 10:06 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1