Oracle database, update data from 1 table to another

Question

I have 2 tables in an oracle 12c database and i want to update from 1 table to the next.

Table 1 (EMBLEMTEMPLATE et):
et.emblemtemplate_id, et.customer_id, et.code

Table 2 (PRODUCTLISTPERCUSTOMER):
plpc.productlistpercustomer_id, plpc.customer_id, plpc.emblemtemplate_id,

What i am trying to do:

Check with all the plpc.productlistpercustomer if there is a plpc.emblemtemplate_id filled, if not this need to be updated.
plpc.emblemtemplate_id needs to be updated with et.emblemtemplate_id where et.code = "999991"
The tables must be joined on CUSTOMER_ID

i created a select:

select plpc.customer_id,
  plpc.productlistpercustomer_id,
  plpc.emblemtemplate_id,
  et.customer_id,
  et.emblemtemplate_id,
  et.code
  from productlistpercustomer plpc
  inner join emblemtemplate et
  on et.customer_id = plpc.customer_id
  where et.code = '999991'

Can somebody help me to translate this to a sql update script?

Thanks!

Visual of what i want to do

Tim Biegeleisen · Accepted Answer · 2020-12-22 06:07:20Z

1

You may try using an update with a correlated subquery:

UPDATE
    productlistpercustomer plpc
SET
    emblemtemplate_id = (SELECT et.emblemtemplate_id
                         FROM emblemtemplate et
                         WHERE et.customer_id = plpc.customer_id AND
                               et.code = '999991')
WHERE
    emblemtemplate_id IS NULL;

answered Dec 22, 2020 at 6:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Valkn Over a year ago

This was perfect!

Littlefoot · Accepted Answer · 2020-12-22 07:02:37Z

0

How about merge?

MERGE INTO productlistpercustomer p
     USING emblemtemplate e
        ON (e.customer_id = p.customer_id)
WHEN MATCHED
THEN
   UPDATE SET p.emblemtemplate_id = p.emblemtemplate_id
           WHERE     e.code = 999991
                 AND p.emblemtemplate_id IS NULL;

answered Dec 22, 2020 at 7:02

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Collectives™ on Stack Overflow

Oracle database, update data from 1 table to another

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related