Duplicate values for another value in the same table

[dlite922]

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:

 
INSERT INTO permission 
(   userID, 
    module, 
    pageAction) 
VALUES 
(   28,
    "reportModule",
    (SELECT pageAction FROM permission WHERE userID = 19)
);
 
 
ERROR 1093 (HY000): You can't specify target table 'permission' for update in FROM clause
 
 

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

[dlite922]

Resolved...by pure accident.

first I tried,

INSERT INTO permission (userID, module, pageAction) VALUES ("28","reportModule", (SELECT pageAction FROM permission WHERE userID = 19));
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!

INSERT INTO permission (userID, module, pageAction) (SELECT "28","reportModule",pageAction FROM permission WHERE userID = 19);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

weird huh!

hope this helps someone else!



Dan

[Atli]

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:

INSERT INTO `myTable`(`userName`, `cola`,`colb`)
SELECT 'NewUser', o.`cola`, o.`colb` FROM `myTable` AS o
WHERE o.`userName` = 'OldUser';
 

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