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?
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.