CODEWITHSUNDEEP
mysql
user1251302
user1251302

Reputation: 91

How do I change a column name in MySQL5

I have this so far but the Column 'name' is ambiguous, so I want to change the name column in the modle table to something else (There is also a name column is the student table). How can I do this? 

SELECT name
FROM student
JOIN modle ON modle.id = event.modle
JOIN event ON event.id = attends.event
JOIN attends ON attends.student = student.id
WHERE name LIKE '%Database%'

Upvotes: 0

Views: 834

Answers (2)

mechanical_meat
mechanical_meat

Reputation: 169494

Column aliases allow you to create your own column names for a particular resultset.
Example: 

SELECT modle.`name` AS model_name, student.`name` AS student_name
FROM student
JOIN modle ON modle.id = event.modle
JOIN event ON event.id = attends.event
JOIN attends ON attends.student = student.id
WHERE name LIKE '%Database%';

If you want to permanently change a column name use the ALTER TABLE command:
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Example:

ALTER TABLE student 
CHANGE COLUMN `name` student_name VARCHAR(50);

http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

Upvotes: 1

Jason McCreary
Jason McCreary

Reputation: 73031

You can prefix the column with the table to avoid ambiguity as well as provide aliases.

For example:

SELECT modle.name, student.name AS student_name FROM …

However, I advocate changing the column name in this case since name is a reserved word in some SQL flavors.

You could do so with an ALTER TABLE command or your database manager.

ALTER TABLE modle CHANGE COLUMN name modle_name [column_definition]

Upvotes: 1

Related Questions