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. On Sunday, January 23, 2022 at 4:28:41 AM UTC+2 euler wrote: > 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 > > On Saturday, January 22, 2022 at 2:40:14 PM UTC+8 euler wrote: > >> 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' >> >> [image: Capture.PNG] >> >> 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 >> > -- All messages to this mailing list should adhere to the Code of Conduct: https://www.lyrasis.org/about/Pages/Code-of-Conduct.aspx --- You received this message because you are subscribed to the Google Groups "DSpace Technical Support" group. To unsubscribe from this group and stop receiving emails from it, send an email to dspace-tech+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/dspace-tech/381a5e5f-76e7-4a8a-9e7a-d00340f90040n%40googlegroups.com.
