Oracle update via join on text values for csv
<p>Some background - I have an exported CSV dataset which will be imported/migrated into a new system. I don't have an identifying ID in this dataset as it's not needed in the new system but i now need to add some additional data into this CSV.</p>

<p>I've imported the CSV into an Oracle table and created 2 new columns - FieldLabelEDRN and ELEMENTID. I want to update those 2 columns with the relevant data from the original tables so i tried the following</p>

<pre><code>UPDATE EDRN_NEW.DATADIC
SET FIELDLABELEDRN =
  (SELECT short_name
  FROM edrn.md$$_all_data_elements
  WHERE active_yn = 'YES'
  ORDER BY short_name
  ),
SET ELEMENTID =
  (SELECT element_id
  FROM edrn.md$$_all_data_elements
  WHERE active_yn = 'YES'
  ORDER BY short_name
  )
</code></pre>

<p>That produces an ora-00907 error and i know it's very basic. I'm struggling to think of a way to get the value i need. I had also tried to join the 2 tables together on the short_name = field label to just pull in the element_id as that's what i really need but i'm not very familiar with not being able to use a JOIN in an UPDATE with Oracle</p>

<p>Any advice/suggestions would be appreciated</p>