phemor phemor - 1 month ago 8
SQL Question

Oracle update via join on text values for csv

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.

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

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
)


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

Any advice/suggestions would be appreciated

Answer

Oracle offers a short-hand for updating multiple columns. However, you need some method of matching the rows in the staging table to the existing table.

The syntax looks like this:

UPDATE EDRN_NEW.DATADIC dd
    SET (FIELDLABELEDRN, ELEMENTID) =
         (SELECT short_name, element_id
          FROM edrn.md$$_all_data_elements ade
          WHERE active_yn = 'YES' AND
                ade.?? = dd.??
         );

But you need some sort of way of matching the rows, so you need to fill in the details in ade.?? = dd.??.

It is also possible that you are confusing UPDATE with INSERT. Do you just want this?

INSERT INTO EDRN_NEW.DATADIC(FIELDLABELEDRN, ELEMENTID)
      SELECT short_name, element_id
      FROM edrn.md$$_all_data_elements ade
      WHERE active_yn = 'YES' ;
Comments