How can I update a column in a table with the result of a select statement that uses row being updated as part of the where clause?

This SQL statement ex开发者_Python百科ample is very close to what I think I need...

 update table1 set value1 = x.value1 from 
      (select value1, code from table2 
       where code = something)  as x 

However, what I need to do is change the "something" in the above example to a value from the row that is being updated.

For example, I tried this but it didn't work:

 update table1 A set value1 = x.value1 from 
      (select value1, code from table2 
       where code = A.something)  as x 

This is a one time operation to update an existing table and I'm not really looking for high performance way to do this. Any solution that gets the task done is good enough.

Edit for clarification

I am using Microsoft SQL Server 6.5.

Edit

I've since had several smart people tell me that many of the answers posted here should have worked. My specific sql statement was way more convoluted than the pseudo sql I used in my example. For some reason the answers I tried (most of them) did not work. The same smart people couldn't figure out why they didn't work either.

The answer I selected did work for me. Future answer seekers should probably look at several other answers below to help resolve their own questions, not just the one that solved my issue.

UPDATE is notoriously different across different DBMSes. But try something along these lines:

UPDATE Table1 SET Value1 =
  (SELECT Value1 FROM Table2 WHERE code = Table1.Something)

Have you tried something like this :-

UPDATE table1 
SET x.Value1=y.Value1
FROM Table1 x INNER JOIN 
Table2 y ON x.Code=y.Something

Try this

UPDATE table1 AS a
SET value1 = x.value1
FROM table2 AS x
WHERE x.code = a.something

Sounds like it should work like this:

Update table1 Set value1 = 
( Select value1
  From table2 
  Where table2.code = table1.something
)

UPDATE Table1, Table2
SET Table1.Value1 = Table2.Value1
WHERE Table2.Code = Table1.Something

Does this help?

Try this one:

update table1
set value1 = x.value1
from table2 x
where something = x.code

"I am using Microsoft SQL Server 6.5"?? Why? Why don't you get the FREE SQL 2008 Express edition or if you qualify, SQL 2008 Developers Edition for about USD50?