Jump to content


Photo

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


  • Please log in to reply
2 replies to this topic

#1 ivanv

ivanv
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 28 August 2003 - 06:33 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 28 August 2003 - 06:50 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 ivanv

ivanv
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 28 August 2003 - 07:43 PM

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

- Ivan V.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users