#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171

    Need advice on INSERT with UNION


    Hello;

    I need to gather data from 2 tables, (each have +1 millin rows ) and insert them in to another table. But I get this:
    #1247 - Reference 'email' not supported (forward reference in item list)
    Code:
     INSERT INTO members_table
                (first_name,
                 last_name,
                 email,
                 phone,
                 date_added)
    SELECT CASE
             WHEN first_name <> '' THEN first_name
             ELSE (SELECT Substring_index(email, '@', 1))
           END         AS first_name,
           last_name   AS last_name,
           email       AS email,
           phone       AS phone,
           date_joined AS date_added
    FROM   members
    UNION
    SELECT CASE
             WHEN firstname <> '' THEN firstname
             ELSE (SELECT Substring_index(email, '@', 1))
           END          AS first_name,
           lastname     AS last_name,
           emailaddress AS email,
           phonenumber  AS phone,
           date_added   AS date_added
    FROM   customers
    So I tried the one below but it does not apply the regular expression funciton, it just inserts the email!
    Code:
     INSERT INTO members_table
                (first_name,
                 last_name,
                 email,
                 phone,
                 date_added)
    SELECT CASE
             WHEN first_name <> '' THEN first_name
             ELSE Substring_index(email, '@', 1)
           END         AS first_name,
           last_name   AS last_name,
           email       AS email,
           phone       AS phone,
           date_joined AS date_added
    FROM   members
    UNION
    SELECT CASE
             WHEN firstname <> '' THEN firstname
             ELSE Substring_index(emailaddress, '@', 1)
           END          AS first_name,
           lastname     AS last_name,
           emailaddress AS email,
           phonenumber  AS phone,
           date_added   AS date_added
    FROM   customers
    Thank you
    Last edited by zxcvbnm; April 25th, 2013 at 05:52 AM.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    your first query did not work because of the SELECT nested inside your CASE expression missing its FROM clause

    as for the second query, i'd have to see the data to figure out why
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Nobbies beach, Gold Coast. It's beautiful.
    Posts
    2,574
    Rep Power
    171
    Originally Posted by r937
    your first query did not work because of the SELECT nested inside your CASE expression missing its FROM clause
    Code:
     INSERT INTO members_table
                (first_name,
                 last_name,
                 email,
                 phone,
                 date_added)
    SELECT CASE
             WHEN first_name <> '' THEN first_name
             ELSE (SELECT Substring_index(email, '@', 1)
                   FROM   members)
           END         AS first_name,
           last_name   AS last_name,
           email       AS email,
           phone       AS phone,
           date_joined AS date_added
    FROM   members
    UNION
    SELECT CASE
             WHEN firstname <> '' THEN firstname
             ELSE (SELECT Substring_index(email, '@', 1)
                   FROM   customers)
           END          AS first_name,
           lastname     AS last_name,
           emailaddress AS email,
           phonenumber  AS phone,
           date_added   AS date_added
    FROM   customers
    #1247 - Reference 'email' not supported (forward reference in item list)
    Originally Posted by r937
    As for the second query, i'd have to see the data to figure out why
    ben@yahoo.com
    test@gmail.com
    alex@ree.net
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    when i said the SELECT was missing the FROM clause, i wasn't suggesting that you supply one, i was explaining the source of the syntax error

    using SELECT is not the way to go

    as for your substring_index problem, check to see what the firstname values are for those rows which you think failed to work properly
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo