What query would I use here?
 

I am wanting to do an update to my post table where I have added a column called usergroupid and I want to run a query on the database so that if the table user has a userid with a usergroupid of 9 I want it to set the usergroupid in the table post to 9 for that same userid.

Any suggestions?

Thanks,
Parker

Good question.

Although

[SQL]
update post SET post.usergroupid = user.usergroupid FROM user WHERE post.userid = user.userid AND user.usergroupid=9;
[/SQL]

is the SQL command (which works in MSSQL and Oracle) you asked, AFAIK MYSQL does not support multi-tabled UPDATE commands. Give it a shot in a test db but I dont think it will work.

Logician:

Sorry. You were correct. This returned a syntax error near 'FROM user WHERE post.userid = user.userid AND user.usergroupid=9' at line 1

Humm.........any other ideas?

Parker

If I'm correct that MYSQL does not support multi-tabled updates you cant do it with SQL (BTW.MYSQL does not support looped SELECTs either).

Your best bet is to write a small PHP script which will go through both tables and update your relevant field.

Logician:

That is exactly what I did to get it to work. I just wrote a small script and all is well.

Thanks for the suggestions.

Parker