-1

I am new to sql. Can someone help me with this requirement.

I have table with 10000 records like this

CompanyID         Name 
300001            A
300004            B
300005            C
300007            D
|
|
|
310000            XXX

And I have a another list of companyIDs that I am going to update the above table(It is just an excel sheet not a table)

OldID       NewID
300001      500001
300002      500002
300003      500003
300004      500004
300005      500005
|
|
310000      510000

My requirement is, If I found the companyID in the first table I need to update it with the NewID and If I didn't find the companyId in the first table I have to create a new row in the table with the NewID regardless of oldID.

Is there any possibility to do both update and insert in a single query?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Siva GV
  • 81
  • 2
  • 13

1 Answers1

1

You're describing an "upsert" or MERGE statement, typically:

merge into table_a
using (<some_statement>)
   on (<some_condition>)
 when matched then
      update
         set ...
 when not matched then
      insert (<column_list>)
      values (<column_list>);

However, a MERGE can't update a value that's referenced in the ON clause, which is what will be required in order to do what you're asking. You will, therefore, require two statements:

update table_to_be_updated t
   set companyid =  (select newid from new_table where oldid = t.companyid )

insert into table_to_be_updated
select newid
  from newtable t
 where not exists ( select 1 
                      from table_to_be_updated
                     where t.newid = companyid )

If it's possible for a newid and an oldid to be the same then you're going to run into problems. This also assumes that your new table is unique on oldid and newid - it has to be unique in order to do what you want so I don't think this is an unreasonable assumption.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Isn't that too complicated? As OP finally wants everything with the new ids. Row generator method would be more suitable. – Lalit Kumar B Sep 09 '15 at 10:51
  • I can't see how a row generator will help @Lalit; the OP already has the data and doesn't want rows generated. – Ben Sep 09 '15 at 10:55
  • Ah! sorry @Ben. I understood incorrectly. i thought OP wants to update all the rows from old_id to new_id as shown in second table. I missed to see he wants to do an upsert. – Lalit Kumar B Sep 09 '15 at 11:06
  • Thanks Ben..seems good.But I have only one table in the Database. The second one is not a table it's just an excel sheet. So, how can I refer the newIDs. Do I need to create a table for that? or any other way is there? Can you pls suggest me. – Siva GV Sep 09 '15 at 11:07
  • @SivaGV You can use an external table for that. But, you need to first save it as CSV or any delimited text file. – Lalit Kumar B Sep 09 '15 at 11:08
  • @SivaGV Also, if those ids are serial numbers as you have shown, then creating a table with such serial numbers would be pretty easy. For example, `WITH new_data(A,b) AS ( SELECT 3000001 a, 5000001 b FROM dual UNION ALL SELECT A+1, b+1 FROM new_data WHERE A < 300010 OR b < 5000010 ) SELECT * FROM new_data;` But test things before doing anything on production :-) – Lalit Kumar B Sep 09 '15 at 11:09
  • @LalitKumarB Actually those are not serial numbers like I have mentioned, those are random ones. Just for reference i have given like that – Siva GV Sep 09 '15 at 11:22
  • @SivaGV Then use an external table, or search how to load data from delimited text file to Oracle database. I am sure there are plenty of examples on this site. – Lalit Kumar B Sep 09 '15 at 11:31
  • @LalitKumarB There are some examples where we can load a csv file into a oracle table using SQL*Loader. But I am using sql+. Do you have any idea to accomplish this in sql+? – Siva GV Sep 09 '15 at 14:34
  • Generally, you can't load a file using SQL\*Plus @SivaGV, you will need to use external tables or SQL\*Loader. If you have SQL*Plus installed you should also have SQL\*Loader and be able to use this. There are plenty of examples on the internet, for instance [Oracle FAQs](http://www.orafaq.com/wiki/SQL*Loader_FAQ). If you have another question about this then please ask one but please bear in mind that "how do you do it" isn't a fantastic question - it's worth trying something first. – Ben Sep 09 '15 at 14:43