Select into problem

oldmatt · #1 (**permalink**) 02-10-11, 18:03

I have the following:

INSERT INTO employees_x_departments(
employees_x_departments_pay_rate,
employees_x_departments_pay_type,
employee_id,
department_id
)
SELECT
DepartmentRate,
DepartmentRateType,
employee_id,
Department
FROM d_view WHERE employee_id IS NOT NULL

The problem is that the DepartmentRateType is a STRING and the employees_x_departments_pay_type field is an ENUM

When I run the above, everythign works good except I get empty strings for the employees_x_departments_pay_type field. When I run just the select portion of the above, I get values that match the ENUM values. Can anyone see what I am missing here?

Thanks for any help you may offer.

it-iss.com · #2 (**permalink**) 02-11-11, 03:40

Hi,

this should work unless the string entry does not exactly match the ENUM value in question. Are you sure there are no extra spaces which would cause this from failing and placing a null in the column?

Code:

create table t1 (name enum('one','two','three'));
create table t2 (name varchar(255));
mysql> insert into t2 (name) values ('one'), ('two'), ('four');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t2;
+------+
| name |
+------+
| one  | 
| two  | 
| four | 
+------+
3 rows in set (0.00 sec)
mysql> insert into t1 (name) select name from t2;
Query OK, 3 rows affected, 1 warning (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 1

mysql> select * from t1;
+------+
| name |
+------+
| one  | 
| two  | 
|      | 
+------+
3 rows in set (0.01 sec)

oldmatt · #3 (**permalink**) 02-11-11, 08:57

Well, I tested that and concluded there was no other characters based on what I tested. But I'll have another look at it.

Thanks for the reassurance!

oldmatt · #4 (**permalink**) 02-11-11, 10:28

Just in case anyone has the same question.

My Field had a '\r' character in it (it camr form a csv file that was generated by excel). When I was trying to check it for line breaks during my initial test, I checked for '\n' and ' ' and '\r\n' but never '\r' by itself.

Thanks for the help!

Internet Services

Web Development

Digital Marketing

Consumer Tech