version 3.23 does not provide for a subselect query.
This was my workaround with MySQL v 3
Run the subselect function below on one table, selecting a foreign key.
(in your case this would be ITEMMAST.ITEMNO from the ITEMMAST table)
Include this with an "IN" statement.
The subselect function produces the selection of foreign keys in parens, like (NULL, 2, 3)
A NULL is automatically added to handle a return of zero rows from the subselect ("WHERE id IN ()" is an error,
"WHERE id IN (NULL)" is OK).
Use it like this:
$thequery="
SELECT *
FROM tableone
WHERE tableone.ID
IN ".
subselect("
SELECT tableoneID
FROM table2
WHERE table2.attribute=$myvalue
");
$result=mysql_query($thequery);
function subselect($query) {
//connect to database before running this
$result = mysql_query($query);
$selectstring = "(NULL";
while ($row = mysql_fetch_array($result)) {
$selectstring .= ", $row[0]";
}
$selectstring .= ")";
return $selectstring;
}
In your specific instance:
$query="update producttest set stock = 'in' where products.internalsku in ". subselect("select ITEMMAST.ITEMNO FROM ITEMMAST INNER JOIN producttest.internalsku on ITEMMAST.ITEMNO = producttest.internalsku where onhand >0 and onhand > onhold");
