SQL not working

[dkwong]

I'm trying to do the following. I have two tables, t_Vendor and Vendor_Load. I want to update the t_Vendor table with values from the Vendor_Load table. Comparing records between the two tables, if the vendor_number_tx matches, then update the fields in that record. If the vendor_number_tx doesn't match, or doesn't exist, insert a new record in the t_Vendor table.

I drafted up the following, which i know isn't efficient. I figure there must be a better way to write it. If you could help me, it would be great! It doesn't work because it says there's a missing expression and one of the sub selects returns more than one row anyway. Would this be better done in a stored proc?

/* update existing records into Vendor table if vendor number exists */
UPDATE t_vendor v
SET v.vendor_number_tx = 
(SELECT vl.vendor_number_tx FROM vendor_load vl WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_short_name_tx = 
(SELECT vl.vendor_short_name_tx FROM vendor_load vl WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_name_tx = 
(SELECT vl.vendor_name_tx FROM vendor_load vl WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_addr2_tx = 
(SELECT vl.vendor_addr2_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_addr3_tx = 
(SELECT vl.vendor_addr3_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_addr4_tx = 
(SELECT vl.vendor_addr4_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_addr5_tx = 
(SELECT vl.vendor_addr5_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx),
    v.vendor_postal_code_tx = 
(SELECT vl.vendor_postal_code_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx)
WHERE
    v.vendor_number_tx = 
(SELECT vl.vendor_number_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx);
    
COMMIT;

/* insert new records into Vendor table if vendor number does not exist */
INSERT INTO t_vendor v
    (v.vendor_id_no, v.vendor_number_tx, v.vendor_short_name_tx, v.vendor_name_tx, v.vendor_addr2_tx, v.vendor_addr3_tx, 
    v.vendor_addr4_tx, v.vendor_addr5_tx, v.vendor_postal_code_tx)
VALUES
    (
    SELECT vl.vendor_number_tx, vl.vendor_short_name_tx, vl.vendor_name_tx, vl.vendor_addr2_tx, vl.vendor_addr3_tx, 
    vl.vendor_addr4_tx, vl.vendor_addr5_tx, vl.vendor_postal_code_tx
    FROM vendor_load vl, t_vendor v
    WHERE v.vendor_number_tx = vl.vendor_number_tx
    AND v.vendor_number_tx <> (SELECT vl.vendor_number_tx FROM vendor_load vl, vendor v WHERE v.vendor_number_tx = vl.vendor_number_tx)
    );
    
COMMIT;

 

[srishan]

>> Would this be better done in a stored proc?

Rather procedurally, not necessarily with a stored proc, if it is a one time deal, I would do it an anonymous PL/SQL block. I am giving an untested code model here which should give you enough direction.

-- For simplicity I have chosen fewer custom 
-- column names. I kept the table names and 
-- joining column name same, FYI.
Declare
 vCnt Number := 0;
Begin
 For rec in(Select vendor_number_tx, name, ssn 
            From vendor_load)
 Loop
  UPDATE t_vendor
  SET    name = rec.name,
         ssn = rec.ssn
  WHERE  vendor_number_tx = rec.vendor_number_tx;
  If sql%notfound Then
    INSERT INTO t_vendor(vendor_number_tx, name, ssn)
    VALUES(rec.vendor_number_tx, rec.name, rec.ssn);
  End if;
  vCnt := vCnt + 1;
  If mod(vCnt, 1000) = 0 Then Commit; End if;
 End loop;
End;
/

Thx,
SriDHAR

 

[dkwong]

Hey SriDHAR,

I gave it a try. I can get the update working, but it's not getting to the insert. Should it be If rec%NOTFOUND? And what's the idea behind If mod(vCnt, 1000) = 0 Then Commit; ?

Thanks

 

[srishan]

>> If the vendor_number_tx doesn't match, or doesn't exist,
>>insert a new record in the t_Vendor table.

You said, You need to Update the record there is a match.
You need to insert the record if there is NO match.

As per this, if you have a matching record from vendor_load table, the record in t_Vendor table would be Updated.
sql%notfund is correct, which means, when you trying to update based on

vendor_number_tx = rec.vendor_number_tx;

And you the match is not found, the sql%notfound becomes TRUE, it is an implicit cursor attribute. Update is done through implicit cursor, FYI.

Now coming to mod(vCnt, 1000) = 0 condition becomes TRUE for every 1000 records of iteration in the loop. Hypothetically I chose 1000 number, you change it to any number you want your COMMIT to occur.

It should work perfectly for your scenario.

I would suggest you to do the following,

create two test tables with less columns, use the structure that I have given in the code.
Test the code.

Let us know how you come out of the test.
Thx,
SriDHAR

 

[srishan]

Here is the test for you,

SQL> select * from test111;

        ID NAME       SSN
---------- ---------- ----------
         1 SriDHAR    99-100
         2 dkWONG     100-101

SQL> select * from test222;

        ID NAME       SSN
---------- ---------- ----------
         1 YO YO      00-00

SQL> Declare
  2   vCnt Number := 0;
  3  Begin
  4   For rec in(Select id, name, ssn
  5              From test111)
  6   Loop
  7    UPDATE test222
  8    SET    name = rec.name,
  9           ssn = rec.ssn
 10    WHERE  id = rec.id;
 11    If sql%notfound Then
 12      INSERT INTO test222(id, name, ssn)
 13      VALUES(rec.id, rec.name, rec.ssn);
 14    End if;
 15    vCnt := vCnt + 1;
 16    If mod(vCnt, 10) = 0 Then Commit; End if;
 17   End loop;
 18   Commit;  -- Please use this as well in your code
 19  End;
 20  /

PL/SQL procedure successfully completed.

SQL> select * from test111;

        ID NAME       SSN
---------- ---------- ----------
         1 SriDHAR    99-100
         2 dkWONG     100-101

SQL> select * from test222;

        ID NAME       SSN
---------- ---------- ----------
         1 SriDHAR    99-100
         2 dkWONG     100-101

Cheers!!
Sri