MySQL : When stored procedure parameter name is the same as table column name

Question

Hello,

Let's say a have a stored procedure SetCustomerName which has an input parameter Name, and I have a table customers with column Name. So inside my stored procedure I want to set customer's name. If I write

UPDATE customers SET Name = Name;

this is incorrect and I see 2 other ways:

UPDATE customers SET Name = `Name`;
UPDATE customers SET customers.Name = Name;

First one works, but I didn't find in documentation that I can wrap parameters inside ` characters. Or did I miss it in the documentation (link is appreciated in this case).

What other ways are there and what is the standard way for such a case? Renaming input parameter is not good for me (because I have automatic object-relational mapping if you know what I mean).

UPDATE:

So, there is a link about backticks (http://dev.mysql.com/doc/refman/5.0/en/identifiers.html) but it's not explained deep enough how to use them (how to use them with parameters and column names).

And there is a very strange thing (at least for me): You can use backticks either way:

UPDATE customers SET Name = `Name`;
//or
UPDATE customers SET `Name` = Name;
//or even
UPDATE customers SET `Name` = `Name`;

and they all work absolutely the same way.

Don't you think this is strange? Is this strange behavior explained somewhere?

Answer 1

Using backticks in MySQL query syntax is documented here:

http://dev.mysql.com/doc/refman/5.0/en/identifiers.html

So yes, your first example (using backticks) is correct.

Answer 2

Here is the link you are asking for: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html The backticks are called "identifier quote" in MySql

Answer 3

I think that your first example is actually backwards. If you're trying to set the "Name" column to the "Name" input parameter, I believe it should be:

UPDATE customers SET `Name` = Name;

And for the second example, you can set table aliases the same way that you do in all other statements:

UPDATE customers AS c SET c.Name = Name;

Answer 4

or use something like that:

BEGIN set @m_query = concat('update users set ',column,' = \'', value,'\' where id = ',user);

prepare stmt from @m_query; execute stmt;

END

column & value are TEXT user are INT