What query would I use here?

[Parker Clack]

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

[Logician]

Good question.

Although


update post SET post.usergroupid = user.usergroupid FROM user WHERE post.userid = user.userid AND user.usergroupid=9;


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.

[Parker Clack]

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

[Logician]

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.

[Parker Clack]

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