UPDATE problem

nferreri

I'm having problems doing a multi table update. Any idea why this wouldn't work?

UPDATE userfield,user SET userfield.field7='Yes' WHERE user.userid=userfield.userid and user.usergroupid=5

getting this error:

You have an error in your SQL syntax near ' user set userfield.field7='Yes' where user.userid=userfield.userid and user.use' at line 1

Almost looks like it isn't recognizing the second table. I'm using PHP 4.2.2

Apparently, this is the only way I can do this because MySQL doesn't support JOINS on update queries... any help would be appreciated.

Thanks!

nnichols

MySQL does not support multiple table updates yet. Will be available in first production release of 4.

nferreri

Oh that's strange... I read in the documentation it should work with version 4.x and I have 4.2

Hmm.. well in any event.. how would I get something like this to work?

I have two tables I need to work with.. here are the fields of interest:

USER

userid
usergroupid

USERFIELD

userid
field7

I want to update all field7='Yes' where there is a matching usergroupid from the USER table.

I was thinking maybe add another column in userfield to store the usergroupid for the corresponding userid but how would I do this? I need to do it for multiple group id's.

Thanks

nferreri

ROFL nevermind on the MySQL version.. I have php 4.2.2...

mysql is 3.23.39

still looking for a solution though.

nnichols

Give this a try -

<?

$sql = "SELECT DISTINCT a.USER_ID
	FROM userfield a, user b
	WHERE b.userid=a.userid";

$result = mysql_query($sql, $db)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

// now we need to create a comma separated list of these userids
while ($row = mysql_fetch_row($result)) {
$temp[] = $row[0];
}
$userid_array = implode(',',$temp);

$sql = "UPDATE userfield SET field7='Yes' WHERE userid IN ($userid_array)";

$result = mysql_query($sql, $db)
	or die('MySQL error: '.mysql_error().'<br>Query: '.$sql);

?>

Hope it helps 😉