Using user variables in UPDATE?

[C Evans]

The MySQL docs say user variables ("@var" style) can be used in most statements. In SET statements a variable is assigned with:

@var=value

whereas in all other statements they are assigned with:

@var:=value

The latter apparently avoids confusion between "=" being used to assign a value, and "=" being used as a comparison.

I have two problems with this.

The examples in the docs use a standalone SET statement to set a variable, using "=". This is fine, but what about the SET clause in an UPDATE statement. Is this a "SET" type statement or not? I'm unclear on which assigment type (= or :=) is correct.

More importantly, i've tried both and I can't get either to work! In the statement below i'm trying to:

a) Extract the first part of a space-delimited string.

b) Put the result in one column, and also use the value to look up
another value in a a second column.

Example
----------
update import i, pricelists p
set @pl:=substring_index(i.pricelist_currency, ' ', 1)
i._pricelist=@pl
i._currency_code= p.currency_code
where @pl=p.pricelist;

This causes a syntax error Whether I use "=" or ":=" to set the variable. What am I missing?

Regards: C