Great! It works. I did have to eliminate the parentheses in the SELECT
part:
INSERT PRIVILEGES (login, Permission_ID)
SELECT ('newuser', Permission_ID)
FROM PRIVILEGES
WHERE login='user1'
had to be
INSERT PRIVILEGES (login, Permission_ID)
SELECT 'newuser', Permission_ID
FROM PRIVILEGES
WHERE login='user1'
Otherwise I got a "Mistake in you SQL syntax error."
Thanks!
-John
on 6/11/04 6:36 AM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
>
> ABSOLUTELY! If you can create a SELECT statement that contains the NEW data
> for the rows you want (regardless of data types. I only used integer
> columns as an example) you can use that in your INSERT statement.
>
> Longer example , smaller tables:
>
> CREATE TABLE USERS (
> login char(8),
> name varchar(20),
> ... (other user information columns)
> )
>
> CREATE TABLE PERMISSION(
> ID int auto_increment,
> name varchar(20) primary key
> )
>
> CREATE TABLE PRIVILEGES (
> login char(8),
> Permission_ID int
> )
>
> I have used a structure like this to provide granular access to various
> applications in the past. The PRIVILEGES table contains one row for each
> type of permission someone has. Examples would be "read public", "read
> confidential", "read secret", "edit public", "edit confidential", edit
> secret", etc. These were the entries in the PERMISSION TABLE. To grant
> someone permission to do certain things an entry in the PRIVILEGES table
> would look like this
>
> INSERT PRIVILEGES (login, Permission_ID) VALUES ('user1', 1)
>
> That would let 'user1' read public documents, get it? One row for each
> level of permission they hold. OK, now I have been asked to duplicate a
> set of permissions (multiple rows in the PRIVILEGES table) because someone
> new was just appointed as the backup to 'user1'. I can write a SELECT
> statement that looks like the rows I want to see like this:
>
> SELECT ('newuser', Permission_ID)
> FROM PRIVILEGES
> WHERE login='user1'
>
> That shows me all of the privileges that 'user1' had but I substituted the
> name of 'new user' as a constant. In reality I could have used any formula
> or a column from another table (by JOIN-ing that table to my FROM clause)
> or any combination of data to create what ever new value I wanted to see in
> that first column. Understand? You can build your results any way you want.
>
> Adding the results of that query to my privileges table is as simple as
>
> INSERT PRIVILEGES (login, Permission_ID)
> SELECT ('newuser', Permission_ID)
> FROM PRIVILEGES
> WHERE login='user1'
>
> The secret to making this work right and NOT screwing up your tables is to
> get the SELECT statement correct *first* then prepend the INSERT clause to
> it so that those result rows end up as new rows in your table. Your
> destination table has an auto-incrementing ID column. You should not insert
> values to that column (yes, you can under certain circumstances but this is
> not one of them) so DO NOT include it in either the INSERT clause or the
> SELECT clause.
>
> Did this help or make it worse?
> Respectfully,
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> John Mistler
> <[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
> phia.net> cc:
> Fax to:
> 06/10/2004 11:12 Subject: Re: COPY row?
> PM
>
>
>
>
>
>
> This ALMOST does it for me, except for the update part. In your example,
> you simply add a number to the value of the column i.e. SELECT (col1 + 1 .
> .
> .) for the update. In my situation, the column to be updated is a string.
> So I need to replace the string value in the VARCHAR column with a new
> string. Is there way to do this?
>
> Thanks,
>
> John
>
> on 6/10/04 12:57 PM, [EMAIL PROTECTED] at [EMAIL PROTECTED] wrote:
>
>>
>> Yes! but you will have to do some typing....
>>
>> Just use the INSERT ... SELECT command. You do NOT want list the PK
> column
>> in your statements (so that it will autoincrement) so you will have to
> type
>> out the rest of the column names. With a table that looks like:
>>
>> CREATE TABLE testme (
>> id int auto_increment primary key,
>> col1 int,
>> col2 int,
>> ...
>> col37 int
>> )
>>
>> You would use a statement like:
>> INSERT testme (col1, col2, ..., col37)
>> SELECT col1, col2, ... , col37
>> FROM testme
>> WHERE <conditions go here>
>>
>> Whatever rows the WHERE clause matched would be added to the table
> creating
>> your duplicate rows. Because you DID NOT list the autoincrement column,
> all
>> of those new rows end up with new numbers.
>>
>> Now, if you know what changes to you want to make at the time of the
>> copying, you can define those changed in the SELECT statement and do it
> all
>> at once.
>>
>> SELECT (col1 + 1, col2 +1, col3, ...
>>
>> That would give you incremented numbers for col1 and col 2 but the rest
> of
>> the fields would be the same, get it?
>>
>> HTH,
>> Shawn Green
>> Database Administrator
>> Unimin Corporation - Spruce Pine
>>
>>
>>
>>
>> John Mistler
>> <[EMAIL PROTECTED] To: <[EMAIL PROTECTED]>
>> phia.net> cc:
>> Fax to:
>> 06/10/2004 03:40 Subject: COPY row?
>> PM
>>
>>
>>
>>
>>
>>
>> Is there a COPY row or DUPLICATE row command? I would like to duplicate
> a
>> row with in a table with 38 columns (auto-incrementing the Primary Key on
>> the copied row, of course) and then alter the entry in ONE of its
> columns.
>> Can this be done without doing a SELECT, then INSERT, then UPDATE?
>>
>> Thanks,
>>
>> John
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>>
>>
>>
>>
>>
>>
>
>
>
>
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
