Adhoc SQL request

Database
KVB
Enthusiast

Adhoc SQL request

CREATE TABLE ORDER_SRC

(

ORDER_ID INTEGER,

ORDER_TEMPLATE_ID INTEGER

);

INSERT INTO ORDER_SRC VALUES(1,3);

INSERT INTO ORDER_SRC VALUES(2,4);

INSERT INTO ORDER_SRC VALUES(3,6);

INSERT INTO ORDER_SRC VALUES(4,1);

INSERT INTO ORDER_SRC VALUES(5,5);

INSERT INTO ORDER_SRC VALUES(6,2);

SEL * FROM ORDER_SRC;

CREATE TABLE ORDER_TGT

(

ORDER_KEY INTEGER,

ORDER_ID INTEGER,

ORDER_TEMPLATE_KEY INTEGER

);

INSERT INTO ORDER_TGT VALUES(10,1,30);

INSERT INTO ORDER_TGT VALUES(20,2,40);

INSERT INTO ORDER_TGT VALUES(30,3,60);

INSERT INTO ORDER_TGT VALUES(40,4,10);

INSERT INTO ORDER_TGT VALUES(50,5,50);

INSERT INTO ORDER_TGT VALUES(60,6,20);

SEL * FROM ORDER_TGT;

SEL

S.ORDER_ID,S.ORDER_TEMPLATE_ID,T.ORDER_ID,T.ORDER_KEY

FROM ORDER_SRC S LEFT OUTER JOIN ORDER_TGT T ON S.ORDER_TEMPLATE_ID=T.ORDER_ID

Source:

ORDER_ID ORDER_TEMPLATE_ID

1 3

2 4

3 6

4 1

5 5

6 2

Expected Target data:

ORDER_KEY  ORDER_ID  ORDER_TEMPLATE_KEY

10 1 30

20 2 40

30 3 60

40 4 10

50 5 50

60 6 20

I need to get ORDER_KEY from target using source.order_template_id=target.order_id and update that order_key in target.order_template_key

For eg: Take ORDER_TEMPLATE_ID from source i.e. 3 and find the match in target which is 3 and get the order_key for that row and that key will be the order_template_key for order_id=1

I have tried using LEFT OUTER JOIN,but there is a confusion araised to solve this.

Please help me in this regard.