Can someone explain why this will not work, or how I can make it work?

I am trying to set a column name as a variable, but MYSQL will display:
ERROR 1054 (42S22): Unknown column 'Col' in 'field list'

So the variable Col is not being set somehow, is there another way to do
this?

Thanks everyone!
-Paul C

Sorry, forgot to list the trigger duh..

delimiter //
CREATE TRIGGER mytrigger
AFTER INSERT ON test
FOR EACH ROW

BEGIN

DECLARE Col CHAR(55);

IF NEW.type = 0 THEN
SET Col = 'news';
ELSE
SET Col = 'pictures';

END iF;


UPDATE trigger_test SET Col = Col + 1 WHERE `userid` = NEW.id;

END;//
delimiter ;

On Oct 30, 11:51 am, nospam-pcart...@atlashosting.com wrote:
> Sorry, forgot to list the trigger duh..
>
> delimiter //
> CREATE TRIGGER mytrigger
> AFTER INSERT ON test
> FOR EACH ROW
>
> BEGIN
>
> DECLARE Col CHAR(55);
>
> IF NEW.type = 0 THEN
> SET Col = 'news';
> ELSE
> SET Col = 'pictures';
>
> END iF;
>
> UPDATE trigger_test SET Col = Col + 1 WHERE `userid` = NEW.id;
>
> END;//
> delimiter ;

You can't use dynamic column names that way. Try something like this
instead:

IF NEW.type = 0 THEN
UPDATE trigger_test SET news = news + 1 WHERE `userid` = NEW.id;
ELSE
UPDATE trigger_test SET pitcures = pictures + 1 WHERE `userid` =
NEW.id;
END IF;

Thank you, I Had a feeling that was the case.

-Paul