Stored Procedure To Update One Table From Another

rodusa

Hi, I need to create a stored procedure that would update the price of an equipment in one table based on another table(main table). But I am having trouble to get the price inside the [if exists select...] and run the update based on this price against the price1 in tablecursor table.

Please help me with any suggestions or modifications in the code bellow. Thanks.

USE mydb
DECLARE @item varchar(40)
DECLARE @desc varchar(255)
DECLARE @price1 varchar(40)

DECLARE TableCursor1 CURSOR FOR
SELECT item_id, description, price1 FROM SN_Equipment

OPEN TableCursor1
FETCH NEXT FROM TableCursor1 INTO @item,@desc,@price1
WHILE @@FETCH_STATUS=0
BEGIN
IF EXISTS(SELECT item_id, price1 FROM TESTDB.TESTDB.dbo.inv_mast as inv_mast_NEW where item_id=@item and price1!=@price1)
begin
PRINT @item+' ---> ' +@price1

-- I want something like this:
-- update TableCursor1 set @price1=inv_mast_NEW.price1
end

FETCH NEXT FROM TableCursor1 INTO @item,@desc,@price1

END
CLOSE TableCursor1
DEALLOCATE TableCursor1

Rodusa

Lars_Berg

There is no need for a cursor, test something like this

use mydb
update SN_Equipment
   set price1 = m.price1 
  from SN_Equipment as  S, 
          TESTDB.TESTDB.dbo.inv_mast as m
 where s.item_id = m.item_id
   and s.price1 <> m.price1

rodusa

How stupid am I trying to for the more complicated solution.

Lars, Thanks for your simplicity.

Rod