Update a table with data from other table with multiple conditions?

user_6941415 0
Viewed 560

I have a table called A and it has ARTICLE_NUMBER and NEWCOLUMN columns. Also, I have another table called B that has ARTICLENUMBER and EVENT columns. I want to execute an update statement for my NEWCOLUMN Column.

If EVENT at Table B is NULL, my NEWCOLUMN column should be 0 and if EVENT at Table B is NOT NULL, my NEWCOLUMN column should be 1.

I've tried the following, but unfortunately it didn't work;

UPDATE A a
INNER JOIN B b 
    ON a.ARTICLENUMBER = b.ARTICLENUMBER
SET
    a.NEWCOLUMN = CASE WHEN b.EVENT IS NULL THEN 0
                       WHEN b.EVENT IS NOT NULL THEN 1
                  END;

Can someone maybe help me?

sql-update join oracle sql
4 Answers

I suspect that you really want EXISTS -- that is to set all values in table A, with 1 if there is a non-NULL matching event. That would be:

UPDATE A
    SET NEWCOLUMN = (CASE WHEN EXISTS (SELECT 1
                                       FROM B
                                       WHERE b.ARTICLENUMBER = a.ARTICLENUMBER AND
                                             b.EVENT IS NOT NULL
                                        )
                          THEN 1 ELSE 0
                      END);

Note that this updates all rows in A -- even those with no matching article in B. As I say, I think this is what you want to do, although it is not exactly how your question is phrased. Your question does not specify what to do for ARTICLENUMBERs that are not in B.

user_11440350

Oracle does not support this MySQL-style update join syntax. But, you may express your update using a correlated subquery:

UPDATE A a
SET NULLBESTAND = (SELECT CASE WHEN b.EVENT IS NULL THEN 0 ELSE 1 END
                   FROM B b
                   WHERE b.ARTICLENUMBER = a.ARTICLENUMBER);
user_18632290

Something like this, I presume:

update a set a.newcolumn = 
  (select case when b.event is null then 0
               when b.event is not null then 1
          end
   from b
   where b.article_number = a.article_number)
where exists (select null from b
              where b.article_number = a.article_number);

Or merge:

merge into a
  using b
  on (a.article_number = b.article_number)
  when matched then update set
    a.newcolumn = case when b.event is null then 0
                       when b.event is not null then 1
                  end;
user_90979060

You could have two updates.

UPDATE A
SET NEWCOLUMN = 0
WHERE ARTICLENUMBER IN (select ARTICLENUMBER from B where EVENT IS NULL);
UPDATE A
SET NEWCOLUMN = 1
WHERE ARTICLENUMBER IN (select ARTICLENUMBER from B where EVENT IS NOT NULL);

As always, make sure you have the proper indexes.

Admin1
Related