XOOPS 
Forum
Forum Index General Forums Community Support Modules Support Themes Support Development International Support Module Hack Reviews Theme Designer Talk
  1. Board index / 
  2. XOOPS Community Support forums / 
  3. Q and A 
  4.  / Some SQL help, please?
Register
1
El_Jordo
Some SQL help, please?

  • 2004/2/12 15:23

  • El_Jordo

  • Just popping in

  • Posts: 64

  • Since: 2003/4/23


I have a number of users in a "staff" group, and I would like to give them all the same rank. The admin interface offers no way to change ranks in bulk, so I'm looking to do some SQL commands.

I can generate a list of the uid's of the staff group by executing this query:
mysql> select uid from xoops_groups_users_link where groupid = 4;

And I can update a user's rank by doing this:
mysql> update xoops_users set rank = 5 where uid = 10;

How do I combine the two so that it automatically updates the rank on all uid's in the results of the first query?

Thanks in advance!
2
fatman
Re: Some SQL help, please?

  • 2004/2/12 20:04

  • fatman

  • Friend of XOOPS

  • Posts: 176

  • Since: 2003/12/13


im not sure the exact syntax that will work, but what you are looking to do is called a subquery. Basically using the results from one query to provid some information to another and running them with one command.

I've tried contructing this for you but im not as smart as I thought, as I got syntax errors myself. But if you search for subqueries via google that might help.

my attempt which doesnt work right..

UPDATE xoops_users SET rank = 5 WHERE ( SELECT *
FROM xoops_users
INNER JOIN xoops_groups_users_link ON xoops_users.uid = xoops_groups_users_link.uid
WHERE groupid = 4 )
3
Mithrandir
Re: Some SQL help, please?

Subqueries are AFAIK handled rather poorly in MySQL, but here goes:
Quote:

UPDATE xoops_users SET rank = [RANK] WHERE uid IN (SELECT uid FROM xoops_groups_users WHERE groupid = [GROUP])

Alternatively, you can take the inner query (select from groups), arrange the uid's as a comma-separated list and run the outer query with the comma-separated list inside the IN () brackets.
4
El_Jordo
Re: Some SQL help, please?

  • 2004/2/12 21:06

  • El_Jordo

  • Just popping in

  • Posts: 64

  • Since: 2003/4/23


Hmm, that didn't work like it should have...

Quote:
mysql> update xoops_users set rank = 5 where uid in (select uid from xoops_groups_users_link where groupid = 4);
ERROR 1064: You have an error in your SQL syntax near 'select uid from xoops_groups_users_link where groupid = 4)' at line 1


Do I need another semicolon for the subquery, perhaps?
5
Mithrandir
Re: Some SQL help, please?

I shouldn't think so, no.

It is one query and should be treated as such.

I found this:http://forums.devshed.com/t75416/s.html

saying Quote:

UPDATE table1, table2 SET rec_count = count(rec_id) WHERE rec_parent = rec_id

which would correspond to
Quote:

UPDATE xoops_users u, xoops_groups_users_link g SET u.rank = 5 WHERE g.groupid = 4

Alternately
UPDATE xoops_users u, xoops_groups_users_link g SET u.rank = 5 WHERE g.uid = u.uid AND g.groupid = 4

Hope that will work.
6
DonXoop
Re: Some SQL help, please?

Sub Selects are tricky at best in MySQL but not really a hinderence to what you want to do. Having multiple queries isn't a problem if you simply script the queries or do it in PHP. You either use an array for temp storage or easier a temporary table.

Store results from the first query and use them for the second.

In the end you might simply run:
mysql XOOPS -p "seed_value" <rank_set.sql
7
Dave_L
Re: Some SQL help, please?

  • 2004/2/12 23:01

  • Dave_L

  • XOOPS is my life!

  • Posts: 2277

  • Since: 2003/11/7


Note that some of the queries posted in this thread are highly version-sensitive.

Full subquery support requires MySQL 4.1+, and multiple tables in an UPDATE query requires 4.0.4+.
8
Mithrandir
Re: Some SQL help, please?

Yup, forgot to include that. Sorry
9
El_Jordo
Re: Some SQL help, please?

  • 2004/2/16 13:54

  • El_Jordo

  • Just popping in

  • Posts: 64

  • Since: 2003/4/23


I've got MySQL 3.23.54. Does that mean I'm out of luck?

Is there a way to do any of this from the phpMyAdmin interface?

Login

Register now!  |  Lost Password?

Search

Advanced Search

Recent Posts

Who's Online

182 user(s) are online (128 user(s) are browsing Support Forums)

Members: 0

Guests: 182

more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Apr 30
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits