Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2006
    Posts
    4

    Question Unanswered: insert with subselect question

    Hello!

    I've got two tables:

    users (id, username, password, entity_type_id, user_fullname_id)
    user_fullnames (id, f_name, m_name, l_name)

    I'm trying to select all the ids from user_fullnames that don't have a corresponding user_fullname_id in the users table, then insert them(along with an entity_type_id of 2 for each one) into the users table.

    INSERT INTO users (entity_type_id, user_fullname_id)
    VALUES (2, (SELECT user_fullnames.id FROM user_fullnames
    WHERE NOT EXISTS (SELECT * FROM users WHERE user_fullnames.id = user_fullname_id)));

    The subselect works, and gives the expected results. But when I use it with the insert, I get "You can't specify target table 'users' for update in FROM clause". If I copy users into a temp table first(or do it with a join instead), I get "Subquery returns more than 1 row". Anyone see what I'm doing wrong?

    Thanks in advance
    ~Thrakazog

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you please show the query where you are able to successfully store the rows you want into a temp table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2006
    Posts
    4

    Question

    > could you please show the query where you are able to successfully
    > store the rows you want into a temp table

    All I'm doing here is copying users into a temp table, to try and get around the "You can't specify target table 'users' for update in FROM clause" error.

    CREATE TEMPORARY TABLE t_users
    SELECT * FROM users;
    INSERT INTO users (entity_type_id, user_fullname_id)
    VALUES (2, (SELECT user_fullnames.id FROM user_fullnames
    WHERE NOT EXISTS (SELECT * FROM t_users
    WHERE user_fullnames.id = t_users.user_fullname_id)));

    thanks,
    ~Thrakazog

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try it like this --
    Code:
    create temporary table newusers
    select user_fullnames.id 
      from user_fullnames
    left outer
      join t_users
        on t_users.user_fullname_id = user_fullnames.id
     where t_users.user_fullname_id is null
    then you can insert newusers into users
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2006
    Posts
    4

    Question

    Close! This give me the data that I need(one column), but then I need to get it into users.user_fullname_id, along with a static value of 2 into users.entity_type_id for each row. This is the part where I get the "subquery returns more than 1 row" error. I could do it in two separate steps, except both user_fullname_id and entity_type_id are non-null.

    thanks!
    ~Thrakazog

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, just take that concept and run with it --

    the table newusers should have the exact same data layout as the users table, and you fill it up with your CREATE/SELECT statement there

    your second step would then be to insert the contents of newusers back into users with an INSERT/SELECT statement, which, since the layouts are identical, will not require mentioning columns --
    Code:
    insert
      into users
    select *
      from newusers
    one of the very rare instances when it is acceptable to use the dreaded, evil "select star"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2006
    Posts
    4
    Thank you! Y'all are greatly appreciated....

    ~T

Posting Permissions

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