Is this possible? WHERE column = (SELECT ...)

[ivanv]

I want to do an UPDATE on certain columns, WHERE a given column matches the result of a query, like this:

 

UPDATE table SET column1 = value WHERE column2 = (SELECT columnx FROM table2 WHERE columny = value)

 

I tried that but it doesn\'t work, so I\'m wondering if it\'s possible to do what I want, and how?

 

Thanks in advance!!

 

- Ivan V.

[Barand]

UPDATE table SET column1 = value WHERE column2 = (SELECT columnx FROM table2 WHERE columny = value)

 

I think subquery support add in MySQL v4.1

 

A work around could be

 

[php:1:dcdad8e5a5]<?php

$sql = \"SELECT columnx FROM table2 WHERE columny = value\";

$result = mysql_query($sql);

while ($row = mysql_fetch_row($result)) {

$array[] = $row[0];

}

$vals = join(\',\' , $array); // if numeric - use next line if string val

// $vals = \"\'\" . join ( \"\',\'\" , $array) . \"\'\"

$sql2 = \"UPDATE table SET column1 = value WHERE column2

IN ($vals)\";

mysql_query $sql2);

?>[/php:1:dcdad8e5a5]

 

hth

[ivanv]

Thanks for your answer... Although I was hoping I could do what I wanted without resorting to another language .

 

- Ivan V.