Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2010
    Posts
    3

    Unanswered: Select into problem

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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)
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Dec 2010
    Posts
    3
    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!

  4. #4
    Join Date
    Dec 2010
    Posts
    3
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •