Using a subquery in an UPDATE

[SnakeHollywood]

Can someone please tell me what is the correct syntax to do this. The way I am doing it seems to be syntactically correct and this is the way it works when you use a subquery within a SELECT statement, but it wont work for an UPDATE statement.<br>There is nothing wrong with my queries except for the subquery.<br><br>example of what I am trying to do.<br><br>UPDATE table1<br>SET name = whatever,<br>&nbsp;&nbsp;&nbsp;&nbsp;field2 = (select a,b from table2 where ID = whatever),<br>&nbsp;&nbsp;&nbsp;&nbsp;field3 = whatever<br>WHERE ID = 1<br>

 

[Guest_imported]

SQL needs to look more like this<br><br>UPDATE Table1 <br>INNER JOIN table2 ON Table1.ID = table2.ID <br>SET Table1.Fieldname = [table2].[A] & [table2].<br>WHERE table1.id = 1;<br>

 

[SnakeHollywood]

Never used inner joins, but from reading that it seems that there has to be a relationship between table1 and table2, which there isn't. I am getting the key values from a form to use as the selection for the records.<br>For instance<br>Table1 contains products<br>table2 contains product types<br><br>In the subquery want to look up the product type for the ID number that was selected on the form and then update the the specified record in the Product table with this product type.<br><br> <p>Russ Michaels<br><a href=mailto:russ@satachi.com>russ@satachi.com</a><br><a href=

http://www.satachi.com>Satachi

Internet Development</a><br>Rapid Web application development, specialising in Coldfusion, database integration and e-commerce solutions.