Translate the German department names

[fortunewalla]

Some of the department names are in German and need English translations.

department column has following fields enum('Marketing','Buchhaltung','Management','Logistik','Flugfeld'), NULL

The employee table description:

mysql> describe employee;
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+
| Field        | Type                                                               | Null | Key | Default | Extra          |
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+
| employee_id  | int                                                                | NO   | PRI | NULL    | auto_increment |
| firstname    | varchar(100)                                                       | NO   |     | NULL    |                |
| lastname     | varchar(100)                                                       | NO   |     | NULL    |                |
| birthdate    | date                                                               | NO   |     | NULL    |                |
| sex          | char(1)                                                            | YES  |     | NULL    |                |
| street       | varchar(100)                                                       | NO   |     | NULL    |                |
| city         | varchar(100)                                                       | NO   |     | NULL    |                |
| zip          | smallint                                                           | NO   |     | NULL    |                |
| country      | varchar(100)                                                       | NO   |     | NULL    |                |
| emailaddress | varchar(120)                                                       | YES  |     | NULL    |                |
| telephoneno  | varchar(30)                                                        | YES  |     | NULL    |                |
| salary       | decimal(8,2)                                                       | YES  |     | NULL    |                |
| department   | enum('Marketing','Buchhaltung','Management','Logistik','Flugfeld') | YES  |     | NULL    |                |
| username     | varchar(20)                                                        | YES  | UNI | NULL    |                |
| password     | char(32)                                                           | YES  |     | NULL    |                |
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+
15 rows in set (0.00 sec)

[fortunewalla]

1. Expand the ENUM column to include new English names also.

ALTER TABLE employee
MODIFY COLUMN department 
enum('Marketing','Buchhaltung','Management','Logistik','Flugfeld',
'Accounting','Logistics','Airfield');

2. Then update the employee data column with English Names.

UPDATE employee
SET department='Accounting'
WHERE department = 'Buchhaltung';
UPDATE employee
SET department='Logistics'
WHERE department = 'Logistik';
UPDATE employee
SET department='Airfield'
WHERE department = 'Flugfeld';

3. Finally update the table employee ENUM to keep only the English names.

ALTER TABLE employee
MODIFY COLUMN
department ENUM(
'Marketing',
'Accounting',
'Management',
'Logistics',
'Airfield'
);

[fortunewalla]

seems to be working

mysql> describe employee;                                                                                                     
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+ 
| Field        | Type                                                               | Null | Key | Default | Extra          | 
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+ 
| employee_id  | int                                                                | NO   | PRI | NULL    | auto_increment | 
| firstname    | varchar(100)                                                       | NO   |     | NULL    |                | 
| lastname     | varchar(100)                                                       | NO   |     | NULL    |                | 
| birthdate    | date                                                               | NO   |     | NULL    |                | 
| sex          | char(1)                                                            | YES  |     | NULL    |                | 
| street       | varchar(100)                                                       | NO   |     | NULL    |                | 
| city         | varchar(100)                                                       | NO   |     | NULL    |                | 
| zip          | smallint                                                           | NO   |     | NULL    |                | 
| country      | varchar(100)                                                       | NO   |     | NULL    |                | 
| emailaddress | varchar(120)                                                       | YES  |     | NULL    |                | 
| telephoneno  | varchar(30)                                                        | YES  |     | NULL    |                | 
| salary       | decimal(8,2)                                                       | YES  |     | NULL    |                | 
| department   | enum('Marketing','Accounting','Management','Logistics','Airfield') | YES  |     | NULL    |                | 
| username     | varchar(20)                                                        | YES  | UNI | NULL    |                | 
| password     | char(32)                                                           | YES  |     | NULL    |                | 
+--------------+--------------------------------------------------------------------+------+-----+---------+----------------+ 
15 rows in set (0.00 sec)                                                                                                     

All rows are in English

mysql> select department, count(department) from employee group by department;                                                
+------------+-------------------+                                                                                            
| department | count(department) |                                                                                            
+------------+-------------------+                                                                                            
| Accounting |               189 |                                                                                            
| Logistics  |               202 |                                                                                            
| Airfield   |               214 |                                                                                            
| Marketing  |               208 |                                                                                            
| Management |               187 |                                                                                            
+------------+-------------------+                                                                                            
5 rows in set (0.00 sec)