I have created a file upload feature that allows users to submit an Excel file containing, for example, only a category name. I can insert the data into the entity, but I would like to either update the existing data or avoid inserting it if a category with the same name already exists in the entity. Could anyone share an example of OML (Oracle Mapping Language) to update the data instead of creating new entries in the entity?
Hi @Jeremy Hui ,1.Loop through the categories in the uploaded file2.Check if the category exists in the databaseUse an Aggregate with a filter Category.Name = ExcelCategory.Name.3.Use an If condition If the aggregate result is empty, create a new category.If it is not empty, update the existing record4.Use CreateOrUpdateCategory Action:If it exists, update its details.If it doesn’t exist, insert a new recordThanks,sahana
Hi Jeremy,
I have attached an .oml file with two valid approaches to avoid inserting a record if a category with the same name already exists in the entity.
Thanks @Beatriz Sabino . this works for me!
Hi @Jeremy Hui ,
If you want SQL-based logic, use an Advanced SQL query to do an Insert or Update.
UPDATE {Category}
SET Name = @CategoryName
WHERE Name = @CategoryName;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO {Category} (Name)
VALUES (@CategoryName);
END IF;
@CategoryName it defines the category name from Excel.
Thanks,
Sudha Narayanan