CODEWITHSUNDEEP
sqloracle-databasesql-update
BigRedEO
BigRedEO

Reputation: 847

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

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;

Upvotes: 0

Views: 3758

Answers (3)

jackkds7
jackkds7

Reputation: 152

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;

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

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;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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
                 );

Upvotes: 2

Related Questions