Can anyone help me re write this sql server code into Oracle PL/SQL code.. ...I am using oracle 8i which is quite old and i am not so good in coding.

UPDATE A
 SET STDCOST = B.Cost
  FROM Table_A A
  INNER JOIN Table_B B
  ON (A.ITEM = B.Item) AND (A.Location = B.Location) ;

 

PS: remember syntax (table_a inner join table_b) dont work in 8i

 

Any expert in oracle 8i.

UPDATE A
SET STDCOST = B.Cost
FROM Table_A A, Table_B B WHERE
A.ITEM = B.Item AND A.Location = B.Location;

Thanks gurkha's but that dont seem to work in oracle 8i...maybe that works on higher version..anyone using oracle 8i..this shit is really frustrating.

piranha,

not sure in 8i, but can you try any one of the followings:

UPDATE
(
SELECT
 A.STDCOST A_STDCOST,
 B.COST B_COST
FROM
 Table_A A,
 Table_B B
WHERE
 A.ITEM = B.Item AND
 A.Location = B.Location 
)
SET
 A_STDCOST = B_COST;

(OR)

MERGE INTO Table_A A USING Table_B B
ON (A.ITEM = B.Item AND A.Location = B.Location)
WHEN MATCHED THEN A.STDCOST=B.COST ;

 

 

Thanks pkshr...it might work in later version and might even work in sql server..It dint work in my case...MERGE feature was introduced in 9i so i dint tried at all..maybe someday id use MERGE....thanks for tips ...... was .8i really sucks..

I really appreciate every ones help...even it could not match my solution i think it will be helpful sometime later..coz most of them might work on later versions...

SOLUTION :  I used the cursor , looped and updated the table A...i hope this would work let me know if you have any other suggestions of comment on my solution.I know it takes too long.

declare

CURSOR cur IS SELECT B.cost cost,A.item item,A.loc loc FROM table_a A, table_b B

WHERE A.ITEM = B.Item AND A.LOC = B.Location;

BEGIN

FOR C1 in cur

LOOP

UPDATE TABLE_A

SET STDCOST = c1.Cost

WHERE item = c1.item

AND loc = c1.loc;

END LOOP;

COMMIT;

END;

FYI: Case Statement dont work on PL/SQL of  oracle 8i ..however it can be used with sql..

visist this site

 

www.asp.net

may be this help u..