SQL Update During importing Data

Question

I have sample data as below format with ID's and name. Name is unique value. Here we can import the data. But during import is that possible in sql query whether the given is already existed then updated the name with old suffix with increment value also along with suffix.

1. In the below data as Jack is already there then during import I need to update it as Jack_old1 and import the new one as Jack with different ID.

2. In the below data as Sino is already there then during import I need to update it as Sino_Old2 and import the new one as Sino with different ID.

ID   Name
--   ------
1    Jack
2    Smith
3    Sino_Old1
4    Sino

whether the above logic can be achieved through sql query with update query as processing time through java is more.

Answer 1

I think you can workaround the ugliness of trying to change names in the case of duplicates. Modify your table to have a timestamp column:

ID | Name  | insert_time
1  | Jack  | '2017-01-12 14:00:00'
2  | Smith | '2017-01-12 14:00:00'
3  | Sino  | '2017-01-12 14:00:00'

Now if a another Jack record is inserted, just insert the new data without changing the name:

ID | Name  | insert_time
1  | Jack  | '2017-01-12 14:00:00'
2  | Smith | '2017-01-12 14:00:00'
3  | Sino  | '2017-01-12 14:00:00'
4  | Jack  | '2017-01-12 14:23:15'

If you want the latest Jack record, you can use the following query:

select *
from yourTable
where Name = 'Jack' AND
      Timestamp = (select max(Timestamp) from yourTable where Name = 'Jack')

It is also possible to write queries to get the second oldest, third oldest, etc., records.