Update Table in Oracle with value from another table with a specific condition

Question

I have two tables in Oracle 11:

Table ta_one:

name    | active
----------------
john    | yes
tina    | yes
mike    | yes

Table ta_two:

name    | active
----------------
john    | yes
tina    | yes
tina    | no
mike    | no

The goal I want to achieve is the following: I want to update the column "active" in the first table ta_one with the value of the column "active" from the second table ta_two under the following conditions:

the name in column "name" in ta_one matches the "name" in ta_two (e.g.: mike and mike)
if there is more than one entry of the same name in ta_two, no update should happen (e.g.: tina)
if there is not a "no" in the column active in table ta_two, no update should happen (e.g.: john)

So the table ta_one should look like this after the update:

name    | active
----------------
john    | yes
tina    | yes
mike    | no

Only mike's entry has changed, because tina is more than one time in the second table and johns entry has a 'yes' in the active column of table ta_two.

Thank you in advance for the help.

Oracle doesn't have an update-join syntax, but [this question](http://stackoverflow.com/q/2446764/2422776), although not an exact duplicate, may give you some pointers. — Mureinik, Jul 27 '16 at 05:21

Tim Biegeleisen · Accepted Answer · 2016-07-27T05:59:35.687

0

I went with always updating with the value 'yes' because your logic will only be doing this.

UPDATE ta_one
SET active = 'no'
WHERE EXISTS
(
    SELECT t2.active
    FROM ta_one t1
    INNER JOIN ta_two t2
        ON t1.name = t2.name
    INNER JOIN
    (
        SELECT name
        FROM ta_two
        GROUP BY name
        HAVING COUNT(*) = 1
    ) t3
        ON t1.name = t3.name
    WHERE t2.active = 'no' AND
          ta_one.name = t1.name
);

edited Jul 27 '16 at 05:59

answered Jul 27 '16 at 05:29

Tim Biegeleisen

502,043
27
286
360

You seem to missing a `where` clause. What if the query inside the `set` clause doesn't return a row? IIUC, this will error out. – Mureinik Jul 27 '16 at 05:38
I have tried it out and it does update 3 rows. The first table ta_one has active = yes at john and an empty value on the other two entries. I think Mureinik is right with his comment. @TimBiegeleisen Ah i see, wil try it. – Gletschernadel Jul 27 '16 at 05:44
@Gletschernadel I updated again, try reloading your page. – Tim Biegeleisen Jul 27 '16 at 05:45
@TimBiegeleisen If I run it, i get 1 updated row, but the table does not change. If I change your 2 `yes` into a `no` it seems to work. – Gletschernadel Jul 27 '16 at 05:52
But changing both to `no` would seem to contradict your logic. Are you sure you worded your question correctly? – Tim Biegeleisen Jul 27 '16 at 05:54
Based on what I read, an update should only happen if `ta_two` contains `yes` for its active value. – Tim Biegeleisen Jul 27 '16 at 05:56
Hmm english is not my native language so its possible. (: I want the "no" from the second table into the first one (following the conditions above) – Gletschernadel Jul 27 '16 at 05:58
If you want to update with `no` values whenever they occur in `ta_two` (along with your other criteria), then my updated query might be what you want. – Tim Biegeleisen Jul 27 '16 at 06:00

score 0 · Answer 2 · answered Jul 27 '16 at 05:55

0

maybe the following will fit:

update 
  ta_one o 
set 
  o.active='no' 
where o.name in (select name from ta_two where active='no') 
and (select count(*) from ta_two where name=o.name)=1

answered Jul 27 '16 at 05:55

Maik Hertel

1

You may replace o.active='no' with o.active=(select active from ta_two where name=o.name), but in this case it's the same. If you do so, there is just one place to edit the value from 'no' to another value (if you have other examples) – Maik Hertel Jul 27 '16 at 06:39

score 0 · Answer 3 · edited Jul 27 '16 at 06:38

0

update ta_one
   set active =
       (select active from ta_two where ta_one.name = ta_two.name)
 where 1 = (select count(1)
              from ta_two
             where ta_two.name = ta_one.name
             group by name)

edited Jul 27 '16 at 06:38

Maverick

1,396
5
22
42

answered Jul 27 '16 at 05:56

Parina

1

score 0 · Answer 4 · answered Jul 27 '16 at 06:11

update ta_one x set active=( select y.active from ta_two y where y.name=x.name
) where not exists ( select 1 from ta_two y where y.name=x.name group by name having count(distinct y.active) > 1 )

Update Table in Oracle with value from another table with a specific condition

4 Answers4