MySQL: when the parameter name of the stored procedure matches the column name of the table

Question

Say you have a stored procedure called SetCustomerName that has an input parameter name, and I have a client table with a column name. Therefore, in my stored procedure, I want to set the client name. If i write

UPDATE customers SET Name = Name; 

this is wrong and I see two other ways:

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

It works at first, but I did not find in the documentation that I can wrap the parameters inside the `characters. Or I skipped this in the documentation (the link is appreciated in this case).

What other methods exist and what is the standard method for such a case? Renaming an input parameter is not suitable for me (because I have an 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 has not sufficiently explained 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 backward measures anyway:

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

and they all work exactly the same.

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

Answer 1

The easiest way to distinguish between your parameter and the column (if both names are the same) is to add the table name to the column name.

 UPDATE customers SET customers.Name = Name; 

Even you can add a database prefix, for example

 UPDATE yourdb.customers SET yourdb.customers.Name = Name; 

By adding a database name, you can perform an action on more than one database from a single storage procedure.

Answer 2

I think your first example is really the opposite. If you are trying to set the “Name” column to the “Name” input parameter, I believe this should be:

 UPDATE customers SET `Name` = Name; 

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

 UPDATE customers AS c SET c.Name = Name; 

Answer 3

The use of backticks in the MySQL query syntax is described here:

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

So your first example (using reverse steps) is correct.

Answer 4

Here is the link you ask for: http://dev.mysql.com/doc/refman/5.0/en/identifiers.html Backticks are called "id quote" in MySql

Answer 5

or use something like this:

 BEGIN set @m_query = concat('update users set ',column,' = \'', value,'\' where id = ',user); prepare stmt from @m_query; execute stmt; END 

Column

and value TEXT user INT