Using CASE in a query..help

[itzleijae]

The goal is to search two similar tables for a single line of data.

UPDATE
    regusers_test AS rt
INNER JOIN
    alt_toon_test AS att
ON
    att.`toonname` = rt.`toonname`
SET
    rt.`server` = CASE WHEN rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' THEN '$server' ELSE rt.`server`
    att.`server` = CASE WHEN att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' THEN '$server' ELSE att.`server`
WHERE
(
    rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon'
)
OR
(
    att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon'
)

But i keep getting:

 

 

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 'att.'server'=Case When att.'username'='$user'...

 

 

I did a lot of google searches over the weekend and couldn't find a solution. Hopefully someone here knows what's up.

Edited July 9, 2013 by itzleijae

[kicken]

You're missing the END for the case statement, as well as your comma between fields.

 

UPDATE regusers_test AS rt
INNER JOIN alt_toon_test AS att ON att.`toonname` = rt.`toonname`
SET
	rt.`server` = CASE 
		WHEN rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' THEN '$server' 
		ELSE rt.`server`
	END
	, att.`server` = CASE 
		WHEN att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' THEN '$server' 
		ELSE att.`server`
	END
WHERE
	(rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon')
	OR (att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon')

[itzleijae]

You're missing the END for the case statement, as well as your comma between fields.

 

UPDATE regusers_test AS rt
INNER JOIN alt_toon_test AS att ON att.`toonname` = rt.`toonname`
SET
	rt.`server` = CASE 
		WHEN rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' THEN '$server' 
		ELSE rt.`server`
	END
	, att.`server` = CASE 
		WHEN att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' THEN '$server' 
		ELSE att.`server`
	END
WHERE
	(rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon')
	OR (att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon')

haha i derped. Thanks!