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

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

> 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

try it like this --then you can insert newusers into users

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

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 --one of the very rare instances when it is acceptable to use the dreaded, evil "select star"

Thank you! Y'all are greatly appreciated....

~T