Gletschernadel Gletschernadel - 4 months ago 7
SQL Question

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

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.

Answer

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