Informatica's Update approach allows you to delete and update records without using SCD.

[mani0525]

Please fix this problem for me:

I have a flat file with data from three places. ( Ahmedabad, Bangalore & Chennai ). This info is being loaded into a database. Insert is easy. However, it should insert/update the new entry and delete from the prior table whenever I change my location in the source, for example, from Bangalore to Chennai, Ahmedabad, or vice versa.
Example:

Location

From Bangalore to Chennai (Updated location) Chennai Ahmedabad

Following Informatica transformation:

Chennai, Chennai, and Ahmedabad

 

[Phro0244]

It seems like you are looking for a way to update the database with new data and delete the old data whenever there is a change in the location source.

One way to achieve this is to use a merge statement or an upsert operation in your database. This will allow you to insert new data, update existing data, and delete old data all in one transaction.

Assuming you are using a relational database like MySQL, you can use a merge statement similar to the following:

MERGE INTO locations AS target
USING (
    SELECT 'Chennai' AS location
    UNION ALL
    SELECT 'Ahmedabad'
) AS source
ON target.location = source.location
WHEN MATCHED THEN
    UPDATE SET last_updated = NOW()
WHEN NOT MATCHED THEN
    INSERT (location, last_updated)
    VALUES (source.location, NOW())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

This statement merges the data from the source table (which contains the new locations) with the target table (which contains the existing locations). It updates the last_updated timestamp for existing locations, inserts new locations, and deletes locations that are no longer present in the source table.

You can run this merge statement periodically using a cron job or a similar scheduling mechanism to keep your database up to date with the latest location data.