Update Multiple Records 1

[prakashroch]

i have two tables one is try3 and other try4

Field try3 ->Itemname,Sales,Itemid
in try3 Itemname and itemid colums are are alredy filled and sales for each record is "Zero" ,there are 100 such records

Field try4 ->Sales,Itemid
Each distinct itemid has sales value

i want to assign itemid from table try4 to records in try3

can it be done in single SQl statemnt or do i have to use loop to read each itemid???

Thanks

 

[branko]

With an update-statement and a join this can be done.
My SQL is getting a bit rusty lately, so I suggest you look in BOL or so for the exact statement

Branko

 

[tlbroadbent]

Update try3
set sales=try4.sales
From try3
Inner Join try4
on try3.itemid=try4.itemid

Note: It is best to stop thinking about "looping" when programming in SQL. SQL is not a procedural language. Though it provides looping contructs, it performs relational operations much more efficiently. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[prakashroch]

Thanks Terry i worked....since i am new to stored procedures i thought i need to read the number of rows and run the update in loop .....also my try4 was a subquery ...i realized that i cant do the inner join if try4 is subquery so decided to make a memory table out of the subquery..
thanks anyways

 

[tlbroadbent]

You can perform a JOIN with sub-queries. Here is an example.

Update try3
set sales=qry.sales
From try3
Inner Join
(Select itemid, sales from try4
where salesdate>dateadd(m,-6,)) qry
on try3.itemid=qry.itemid
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.