Insert or update table

IamtheWalrus

I have a list of 100k ids in a file. I want to iterate through these ids, for each id, check is the id in a table in an Oracle database.

If it is, updated its updated_date flag. If not, add a new record (id, updated_date).

I have researched and found 'merge'. The downside is, merge requires the ids to be in a table. I am only allowed to create a temporary table if necessary.

Can anyne point me in the right direction? It must be a script that I can run on my database, not in code.

merge into MyTable x
using ('111', '222', all my ids) b
on (x.id = b.id)
when not matched then insert (id, updated_date) values (b.id, sysdate)
when matched then update set x.updated_date = sysdate

croo

So if the id is in a table then update the date if it's not insert the id & date.
why not just drop the table (or delete all the records which ever suits) and then insert ALL the ids & current date?

Berserker

What type of file is it? Can you import it into a database? Alternatively, you could just open the file in your db script and update the records. Not an Oracle person myself but I have done this in SQL Server before.

Colonel Panic

Iterating and SQL? Does not compute!!

Load the IDs, update the table where the table ID is in the list, insert where the table ID is not in the list.

Zith

Depending on the Oracle version you could possibly treat the file of IDs as an external table. That can then be used directly in the merge.

IamtheWalrus

My requirements changed and I no longer require this feature. Thanks for the answers.