Syntax:
SET PASSWORD [FOR user] = PASSWORD('some password')

The SET PASSWORD statement assigns a password to an existing MySQL user
account.

With no FOR clause, this statement sets the password for the current
user. Any client that has connected to the server using a non-anonymous
account can change the password for that account.

With a FOR clause, this statement sets the password for a specific
account on the current server host. Only clients that have the UPDATE
privilege for the mysql database can do this. The user value should be
given in user_name@host_name format, where user_name and host_name are
exactly as they are listed in the User and Host columns of the
mysql.user table entry. For example, if you had an entry with User and
Host column values of 'bob' and '%.loc.gov', you would write the
statement like this:

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

URL: http://dev.mysql.com/doc/refman/5.0/en/set-password.html

