By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,426 Members | 1,729 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,426 IT Pros & Developers. It's quick & easy.

Duplicate values for another value in the same table

dlite922
Expert 100+
P: 1,584
I have a permission table that gives a userID permission to a module and the any actions within that module.

What I want to do is duplicate his permissions to another user.

Permission table has:

id (PK)
userID (FK)
module
action

When I do an insert and do a select of the values of the first user, it says It cannot use the target table in the FROM clause.

Query:
Expand|Select|Wrap|Line Numbers
  1.  
  2. INSERT INTO permission 
  3. (   userID, 
  4.     module, 
  5.     pageAction) 
  6. VALUES 
  7. (   28,
  8.     "reportModule",
  9.     (SELECT pageAction FROM permission WHERE userID = 19)
  10. );
  11.  
  12.  
  13. ERROR 1093 (HY000): You can't specify target table 'permission' for update in FROM clause
  14.  
  15.  
I've tried a couple of varieties of that same INSERT line with no luck. Is this possible?

What's interesting is the Manual is very confusing, observe these statments from the manual: http://dev.mysql.com/doc/refman/5.0/...rt-select.html

[HTML]

# The target table of the INSERT statement may appear in the FROM clause of the SELECT part of the query. (This was not possible in some older versions of MySQL.) In this case, MySQL creates a temporary table to hold the rows from the SELECT and then inserts those rows into the target table.

# Currently, you cannot insert into a table and select from the same table in a subquery.

# To avoid ambiguous column reference problems when the SELECT and the INSERT refer to the same table, provide a unique alias for each table used in the SELECT part, and qualify column names in that part with the appropriate alias.
[/HTML]


Dan
Oct 17 '08 #1
Share this Question
Share on Google+
2 Replies


dlite922
Expert 100+
P: 1,584
Resolved...by pure accident.

first I tried,

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO permission (userID, module, pageAction) VALUES ("28","reportModule", (SELECT pageAction FROM permission WHERE userID = 19));
  2. ERROR 1093 (HY000): You can't specify target table 'permission' for update in FROM clause
then out of frustration, i removed "VALUES" and it worked!


Expand|Select|Wrap|Line Numbers
  1. INSERT INTO permission (userID, module, pageAction) (SELECT "28","reportModule",pageAction FROM permission WHERE userID = 19);
  2. Query OK, 5 rows affected (0.00 sec)
  3. Records: 5  Duplicates: 0  Warnings: 0
weird huh!

hope this helps someone else!



Dan
Oct 17 '08 #2

Atli
Expert 5K+
P: 5,058
weird huh!
If you look closely, the manual says nothing about a VALUE clause. It is only meant to be used with the *normal* version of the INSERT statement.

The INSERT ... SELECT syntax is meant to be used like so:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO `myTable`(`userName`, `cola`,`colb`)
  2. SELECT 'NewUser', o.`cola`, o.`colb` FROM `myTable` AS o
  3. WHERE o.`userName` = 'OldUser';
  4.  
Which would duplicate the two columns from the old user for the new user.

... which is pretty much the same thing you did in the end there :P
Oct 17 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.