Update MYSQL table based on value in other table

[billinboston]

I have been trying to update a table based on whether the values in a column equal a value in separate table within the database. 

 

All I have been able to do is the basic set value based on value inputed into the php code.  If someone knows how to query a value from a separate table and then use it to compare to the first table that would be greatly appreciated.  Thanks guys.

 

 

<?php

$con = mysql_connect("server","database","%*&^(*&(*&");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("database", $con);

 

 

mysql_query("UPDATE data SET Column_name = 'Value'

WHERE column_name = **value in another table** ");

 

 

mysql_close($con);

?>

 

 

[bwcc]

You can run two query strings or use a nested query.  The nested query is below, then the two query strings below that.

 

Nested:

<?
// Your connection string
mysql_select_db("DATABASE", $con);

mysql_query("UPDATE data SET Column_name = 'Value' 
WHERE column_name = (SELECT some_column FROM some_table LIMIT 1)");

mysql_close($con);
?>

2 queries:

<?
// Your connection string
mysql_select_db("DATABASE", $con);

$row=mysql_fetch_array(mysql_query("SELECT some_column FROM some_table WHERE some_condition LIMIT 1"));
$query=mysql_query("UPDATE data SET Column_name = 'Value' 
WHERE column_name = ' ".$row['some_column']." ' ");

mysql_close($con);
?>

 

[fenway]

Or you can do a multi-table update statement as of v4.0+.