Home » SQL & PL/SQL » SQL & PL/SQL » Update/insert (Oracle 9i)
Update/insert [message #425484] Fri, 09 October 2009 05:27 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have a parking table named parking_tab where data is populated at intervals to be updated to table trans_tab. The table create script with some inserts:-
CREATE TABLE trans_tab
( 
  VENDOR_ID                varchar2(20)         NOT NULL,
  ITEMCODE                 VARCHAR2(50 BYTE)    NOT NULL,
  ACTIVATION_FLAG          CHAR(1 BYTE)
)                                  

CREATE TABLE PARKING_tab
(
  VENDOR_CODE         VARCHAR2(20 BYTE)         NOT NULL,
  ITEM_CODE           VARCHAR2(20 BYTE),
  VENDOR_ITEM_STATUS  CHAR(1 BYTE)
)

insert into trans_tab (vendor_id, itemcode, activation_flag) 
  values ('1','100','Y')
insert into trans_tab (vendor_id, itemcode, activation_flag) 
  values ('2','100','Y')
insert into trans_tab (vendor_id, itemcode, activation_flag) 
  values ('2','200','Y')

insert into parking_tab (vendor_code, item_code, vendor_item_status) 
  values ('1','100','Y')
insert into parking_tab (vendor_code, item_code, vendor_item_status) 
  values ('1','200','Y')



There would be many records in both tables.
I have to update the table trans_tab with changes in parking_tab. This parking_tab would contain all records that is it would have all current updated records and same need to be refreshed to the table trans_tab.

A vendor_id can be mapped to more than one item_id.
And there would be a vendor_item_status telling whether the particular combination of vendor and item is active with a flag.

Updates has to be done to the trans_tab accordingly only for the records where vendor_item_status = 'N'.

There may be some new records or combination of new vendor_id and item_id which need to be inserted in the trans_tab table.

I am not sure how to update the status for the records where vendor_id = vendor_code and item_id_item_code in both tables.

I try using this but it updates all records.
UPDATE tab1 smp
      SET (activation_flag)   =                
         (
         SELECT park.vendor_item_status
            FROM parking_tab park, tab1 smp
           WHERE park.vendor_code = smp.vendor_id
             AND park.item_code   = smp.itemcode 
      AND UPPER(park.vendor_item_status)       = 'N'
      )
     


Also for the records which won't match for both the vendor and item, how can I insert those.

Then there is one option that I loop through the records and match one by one and then do update or insert...but it would required lot of select statement in each looping.

Kindly, suggest something which can be done keeping in mind the performance too.

Regards,

Mona
Re: Update/insert [message #425487 is a reply to message #425484] Fri, 09 October 2009 05:39 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
You can use Merge Commmand.


[Updated on: Fri, 09 October 2009 05:41]

Report message to a moderator

Re: Update/insert [message #425489 is a reply to message #425487] Fri, 09 October 2009 05:43 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Inserts can be done by merge.
What about the records that require the status to be changed?

I haven't used merged so not sure about updates.

I have to update based on a condition that update need to be done only for status = 'N'.

[Updated on: Fri, 09 October 2009 05:48]

Report message to a moderator

Re: Update/insert [message #425507 is a reply to message #425489] Fri, 09 October 2009 06:16 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member

XE@SQL> create table test_1 (x int ,y varchar2(1));

Table created.

XE@SQL> create table test_2 (x int ,y varchar2(1));

Table created.

XE@SQL> insert into test_1 select rownum, 'Y'   from dual connect by level <=5;

5 rows created.


XE@SQL> insert into test_2 select rownum,
  2  case when rownum in (3,5) then 'Y' else 'N' end
  3  from dual connect by level <= 7;

7 rows created.



XE@SQL> select * from test_1;

         X Y
---------- -
         1 Y
         2 Y
         3 Y
         4 Y
         5 Y

5 rows selected.


XE@SQL> select * from test_2;

         X Y
---------- -
         1 N
         2 N
         3 Y
         4 N
         5 Y
         6 N
         7 N

7 rows selected.


XE@SQL>   merge into test_1
  2    using test_2
  3    on (test_1.x = test_2.x)
  4    when matched
  5    then update set test_1.y = case when test_2.y = 'N' then test_2.y
  6                               else test_1.y end
  7    when not matched
  8    then
  9    insert (x,y) values (test_2.x,test_2.y)
 10  /

7 rows merged.


XE@SQL> select * from test_1;

         X Y
---------- -
         1 N
         2 N
         3 Y
         4 N
         5 Y
         6 N
         7 N

7 rows selected.

XE@SQL>




In 10G you could also have done like this


XE@SQL> merge into test_1
  2  using test_2
  3  on (test_1.x = test_2.x)
  4  when matched
  5  then update set test_1.y = test_2.y
  6  where test_2.y = 'N'
  7  when not matched
  8   then
  9  insert (x,y) values (test_2.x,test_2.y)
 10  /

5 rows merged.

XE@SQL> select * from test_1;

         X Y
---------- -
         1 N
         2 N
         3 Y
         4 N
         5 Y
         6 N
         7 N

7 rows selected.

XE@SQL>





Re: Update/insert [message #425513 is a reply to message #425507] Fri, 09 October 2009 06:43 Go to previous message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Thanks Bonker for your help.
I was able to do this and it merged beautifully.

MERGE INTO trans_tab
   USING parking_tab
   ON (    trans_tab.vendor_id = parking_tab.vendor_code
       AND trans_tab.itemcode = parking_tab.item_code)
   WHEN MATCHED THEN
      UPDATE
         SET trans_tab.activation_flag =
                CASE
                   WHEN parking_tab.vendor_item_status = 'N'
                      THEN parking_tab.vendor_item_status
                   ELSE trans_tab.activation_flag
                END
   WHEN NOT MATCHED THEN
      INSERT (vendor_id, itemcode, activation_flag)
      VALUES (parking_tab.vendor_code, parking_tab.item_code,
              parking_tab.vendor_item_status)


Thanks again.
Previous Topic: How to export packages???
Next Topic: slow store procedure (merged 4)
Goto Forum:
  


Current Time: Sat Dec 03 12:28:28 CST 2016

Total time taken to generate the page: 0.09350 seconds