SQL UPDATE data from one column with a match from another column

Question

I have two SQL tables - one table MODEL with only three columns and the other my MASTER table with all data. I have added a new Column (MODEL_LONG) to the MASTER table. I now need to update that new column with data from the MODEL table.

The MODEL table only has about 40 records. The first column is the MODEL column, the other two are MODEL_SHORT (abbreviated names for the model) and MODEL_LONG (the full model names).

I need to populate the new MODEL_LONG Column I just added to the MASTER table. I need to check what the MODEL name is in each record of the MASTER TABLE and update the MODEL_LONG Column on the MASTER table with the corresponding MODEL_LONG name in the MODEL table.

The following is not working (in Oracle SQL Developer). What am I missing here?

UPDATE MASTER
SET MASTER.MODEL_LONG = MODEL.MODEL_LONG
FROM MASTER JOIN MODEL
ON MASTER.MODEL = MODEL.MODEL;

This question has been asked and answered many times: https://stackoverflow.com/questions/2446764/update-statement-with-inner-join-on-oracle — sgeddes, Jun 08 '18 at 14:43
Interestingly, I got my UPDATE query from here - https://stackoverflow.com/questions/5036918/update-one-table-with-data-from-another — BigRedEO, Jun 08 '18 at 14:47

score 2 · Answer 1 · answered Jun 08 '18 at 14:42

2

Oracle doesn't support the FROM clause in UPDATE. One method uses correlated subqueries:

UPDATE MASTER
    SET MASTER.MODEL_LONG = (SELECT MODEL.MODEL_LONG
                             FROM MODEL
                             WHERE MASTER.MODEL = MODEL.MODEL
                            );

This will update all rows. Those with no matches get NULL. To prevent that:

UPDATE MASTER
    SET MASTER.MODEL_LONG = (SELECT MODEL.MODEL_LONG
                             FROM MODEL
                             WHERE MASTER.MODEL = MODEL.MODEL
                            )
    WHERE EXISTS (SELECT MODEL.MODEL_LONG
                  FROM MODEL
                  WHERE MASTER.MODEL = MODEL.MODEL
                 );

answered Jun 08 '18 at 14:42

Gordon Linoff

1,242,037
58
646
786

I was using the query from here (first answer), but I guess that was specific to SQL Server? https://stackoverflow.com/questions/5036918/update-one-table-with-data-from-another – BigRedEO Jun 08 '18 at 14:48
Getting this error with this query - SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" – BigRedEO Jun 08 '18 at 15:23
@BigRedEO . . . Why would `MODEL` have duplicate values in the column `MODEL`? One would expect the linking column to be unique. To solve the problem with duplicates, you need to explain what to do in the event of duplicates. – Gordon Linoff Jun 09 '18 at 02:45

jackkds7 · Answer 2 · 2018-06-08T17:03:32.977

1

You can use Merge Statement for updating the MASTER TABLE

MERGE INTO MASTER 
USING (SELECT * FROM MODEL) MODEL
ON (MASTER.MODEL = MODEL.MODEL)
WHEN MATCHED THEN UPDATE 
SET MASTER.MODEL_LONG = MODEL.MODEL_LONG;

Try to use distinct keyword in the inner query because ORA-30926 Error means you source table contains duplicate ID/Records. Just put distinct and see if it helps.

select distinct * from MODEL;

edited Jun 08 '18 at 17:03

answered Jun 08 '18 at 14:46

jackkds7

132
1
7

Getting the following error with this version - SQL Error: ORA-30926: unable to get a stable set of rows in the source tables 30926. 00000 - "unable to get a stable set of rows in the source tables" *Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause. *Action: Remove any non-deterministic where clauses and reissue the dml – BigRedEO Jun 08 '18 at 15:22

score 0 · Answer 3 · answered Jun 08 '18 at 15:28

0

If your tables have appropriate unique keys, you can use this one:

UPDATE 
   (SELECT 
        MASTER.MODEL_LONG as MASTER_MODEL_LONG, 
        MODEL.MODEL_LONG as MODEL_MODEL_LONG
    FROM MASTER JOIN MODEL ON MASTER.MODEL = MODEL.MODEL)
SET MASTER_MODEL_LONG = MODEL_MODEL_LONG;

answered Jun 08 '18 at 15:28

Wernfried Domscheit

54,457
9
76
110

I created the MASTER table, but someone else created the MODEL table. I am beginning to suspect they may have used quotes around the column names when creating it, which might be the reason for the errors I'm getting no matter which UPDATE statement I try. – BigRedEO Jun 08 '18 at 16:36

SQL UPDATE data from one column with a match from another column

3 Answers3