SQL query to Insert value to a field based from another field if it doesn't exisst

[euler]

Dear SQL experts,

I am planning to insert value to a field if and only if it doesn't exist. For example, there are items that have the entry 'Hypophthalmichthys nobilis' in the dc.subject field. I would like to duplicate this entry to another new field dc.subject.scientificName.

I have issued this SQL query to list the items in the dc.subject containing 'Hypophthalmichthys nobilis":

select distinct dspace_object_id, metadata_field_id, text_value, authority, confidence from metadatavalue where metadata_field_id=57 and text_value='Hypophthalmichthys nobilis'

I wonder what would be the SQL query to insert 'Hypophthalmichthys nobilis' found in dc.subject with metadata_field_id=57 to another field dc.subject.scientificName with metadata_field_id=155 if it doesn't exist yet?

My example above is only 4 items but I have hundreds of items to that I need to update also. Previously, my method is to export the metadata containing the field I want to insert, batch edit the CSV, import it, and then do the SQL update, which is quite tedious especially during the batch edit because dc.subject contains other terms separated by double pipes (||). I thought there must be an easier way to do this via SQL.

DSpace version is 6.3 by the way.

Thanks in advance and best regards,

euler

[euler]

After doing some Googling around, I managed to insert the value ' Hypophthalmichthys nobilis' into a new field with metadata_field_id=155. Below is my SQL:

insert into metadatavalue (metadata_field_id,authority,text_value,dspace_object_id,confidence)
select 155,163692,text_value,dspace_object_id,600

from metadatavalue where metadata_field_id=57 and text_value='Hypophthalmichthys nobilis';

However, I wonder what statement should I add to check if the value already exists because running the SQL statement above will create a duplicate value.

Thanks in advance!

euler

[Evelthon Prodromou]

Hello euler,

I haven't gone through your problem in detail, but UPSERT might offer some assistance?

Something like below:

INSERT INTO table_name(list of columns) VALUES(list of values) ON CONFLICT (column_name) DO NOTHING;

Again, this is not tested, and I could be wrong.

Hope it helps.

E.