MySQL: How do I update one a table by using join

[grahamstewart]

I'd like to do this...

[pre]
update user, usergroup 
    set privs = privs ¦ 16 
    where usergroup.user = user.login and usergroup.member_of = 5
[/pre]

i.e I'd like to update the privs column of the user table for all members of a usergroup

That works just fine in MySQL 4.x but I've just discovered my host runs MySQL 3.23 which doesn't allow multiple tables in an update statement.

So how can I reformulate that statement?

I guess I need something like

[pre]
update user set privs = privs ¦ 16 
  where login = 
    ( select login from user,usergroup
       where usergroup.user = user.login and usergroup.member_of = 5 )
[/pre]

But I don't seem to be able to get the syntax quite right.
What am I missing?

[Birdman]

Do you have a privs column for both tables? I'm not an advanced SQL guy, but it seems like you should only need to update one table.

UPDATE user SET..

Forgive me if I'm off :)

[bcc1234]

...where login in (....

It's slower, but a correct way to do this kind of an update.

[grahamstewart]

Birdman: privs is only in the user table. So yeah I only need to update one table - but the rows that I want to update depend on the values in the other table.

e.g.

update user set privs = priv ¦ 16

would update all users and I only want to update those who are in a given usergroup.

bcc1234:

It's slower, but a correct way to do this kind of an update.

Fair enough... but how? I don't seem to be able to get the syntax right for the sub-query. just realised I could just use...

update user set privs = privs ¦ 16
where user.login in (
select usergroup.login from usergroup where usergroup.member_of = 5 )

but thats crashing phpMyAdmin!

[grahamstewart]

Sorry, just realised this should be unser PHP forum _ I forgot you spplit them up...!summon admin

[ggrot]

There is no way to do updates depending on data in another table within mysql. There are ways (I think - could look them up if you wanted) in other flavors of SQL, but not mySQL. You are forced to do some kind of script that parses the results of a select and farms out the resulting updates.

[bcc1234]

There is no way to do updates depending on data in another table within mysql. There are ways (I think - could look them up if you wanted) in other flavors of SQL, but not mySQL. You are forced to do some kind of script that parses the results of a select and farms out the resulting updates.

Are you sure about that?
I don't know cause I don't use MySQL, but that's a nice thing to know.

[grahamstewart]

There is no way to do updates depending on data in another table within mysql.

Well there is... the update with two tables in it works perfectly in MySQL 4.. but just not in 3. :(

Oh well, I'll just do it in a script. Thanks