update based on select statement

sorry for writing value1 etc..field1 , field2 are columns from table Other
Update Test
set va1 = field1
, va2 = field2
from Other
where ........

pra_kumar03,

in where clause, it may return more than 1 row. How do I tell it to do a update based on only first row.

I find that using a joined table update is the easiest.
example:
Update Test Inner Join Other On
Test.TPK = Other.OPK
Set
 Test.va1 = Other.va1,
 Test.va2 = Other.va2,
 Test.va3 = Other.va3

======================
You should also be able to do something like the following:
Update Test
Set
 Test.va1 = (Select Other.va1 From
Other Where Test.TPK = Other.OPK)
 Test.va2 = (Select Other.va2 From
Other Where Test.TPK = Other.OPK),
 Test.va3 = (Select Other.va3 From
Other Where Test.TPK = Other.OPK)

well it only update based on the first row returned.. you may like to cut down the number of rows based on the where clause.

pra_kumar03,

can you give me an example for cutting down the row ?

let me have the exact query that you have.
the where clause should actually filter out the records and return only one record.

Test1 table :

ID  Item  Text  Number
--  ------  -----  ---------
1   101    111   1001
2   102    222   1002
3   103    333   1003

First, I will insert a row into this table with this data:

ID = 4
Number = 1001

Now Test1 is :

ID  Item  Text  Number
--  ------  -----  ---------
1   101    111   1001
2   102    222   1002
3   103    333   1003
4                     1001

Now, I want to do a update based on Number 1001,
so 101 and 111 will go to row's ID 4.

I just want to know how to use update-select to acheive this.

I don't want multiselect on one update.

Update Test1 Inner Join Test1 As Other On
Test1.Number = Other.Number
Set
 Other.[Item] = Test.[Item] ,
 Other.[Text]  = Test.[Text]
Where Test1.ID = (Select Min(T2.ID) From Test1 As T2 Where T2.Number = 1001)
And
Test1.ID < Other.ID
And
Other.ID = (Select Max(T3.ID) From Test1 As T3 Where T3.Number = 1001)
And
 Other.[Item] Is Null
And
Other.[Text] Is Null

nicolle,

You may not need all of the Where clause conditions, depending on which rows you want to update.  For instance, you might need update several rows with Number=1001, so you would remove the condition that the Other.ID be the maximum ID with Number=1001.

namasi_navaretnam,

Cool ! This is what I am looking for. Clear and easy to follow.