Need help with MySQL Query

[711]

I would like to run a mysql query to copy the exact forum permissions from one usergroup to another. Ideally, when query is complete, the two usergroups will have identical permissions for all forums and subforums.



I am far from a SQL guru but I maybe my feeble attempt can give some idea of what I would like to do:



usergroupid = 2 {source usergroup whose permissions I would like to copy}

usergroupid = 83 {target usergroup whose permissions I would like to overwrite}



Step 1:

PHP Code:


SELECT forumpermissions FROM forumpermission WHERE usergroupid = 2; 


Step 2: This is where I am stuck

PHP Code:


UPDATE forumpermission SET forumpermissions = {the ones selected above} WHERE usergroupid = 83; 


Any help would be greatly appreciated.



Thanks!

[WhaLberg]

PHP Code:

<?php

$query = $db->query_read("SELECT forumpermissions FROM forumpermission WHERE usergroupid = 2");
$result = $db->fetch_array($query);
$secondquery = $db->query_write("UPDATE forumpermission SET forumpermissions = " . $result['forumpermissions'] . " WHERE usergroupid = 83");

?>

[711]

Sorry, I should have specified, can you give the code in pure SQL format? I will run the query directly from MySQL query Browser, not within a vBulletin or php script.

Thanks for the quick reply!!!!

[Analogpoint]

Something like this should do: (Back up your board first)
[sql]UPDATE forumpermission as permwrite, forumpermission as permread
SET permwrite.forumpermissions = permread.forumpermissions
WHERE permwrite.usergroupid = 83 AND permread.usergroupid = 2[/sql]

That should change usergroup 83's forum permissions to match usergroup 2's.

[711]

That query returns an error.

Even when I try just this:

PHP Code:

UPDATE forumpermissions as permwrite, forumpermissions as permread ; 


or this

PHP Code:

UPDATE forumpermissions as permwrite; 


All return errors.

I am using MySQL 4.1.22, perhaps 'UPDATE ... AS' is not supported in that version?

I couldn't find reference to that contruction anywhere in the mysql online documentation (not even for mysql 5).

Thanks for trying in any case!

Any ideas?

[Analogpoint]

I tested it on MySQL 5.0.21.

Now, what do you want to do, just update forum permissions one usergroup and make it the same as another one, or do you need to do this for many usergroups?

[711]

Originally Posted by Analogpoint

I tested it on MySQL 5.0.21.

Now, what do you want to do, just update forum permissions one usergroup and make it the same as another one, or do you need to do this for many usergroups?

I would like to copy all forum permissions from one usergroup (usergroupid = 2) to another (usergroupid = 54).

I need to copy those permissions for several usergroups, but if we can get the query to work successfully on group 54, then I can simply run the query again for the others.

[Analogpoint]

The table is actually `forumpermission` (with no s, my bad). Can you try this again?
[sql]UPDATE forumpermission as permwrite, forumpermission as permread
SET permwrite.forumpermissions = permread.forumpermissions
WHERE permwrite.usergroupid = 83 AND permread.usergroupid = 2[/sql]

[Eikinskjaldi]

Originally Posted by 711

That query returns an error.

Any ideas?

One idea, please let us know exactly what error it returns. The more detail you provide, the more help we can provide.