CODEWITHSUNDEEP
mysqlsql-updateconflict
Memor-X
Memor-X

Reputation: 2970

Update value from Inner Join with mutiple results in mySQL

i have an update query something like this

UPDATE table_a AS a
    INNER JOIN table_b AS b
        ON a.id = b.id
SET a.category = b.category

table_a is a temp table that's uses to format data for an import file table_b is one of a few tables which i'm compiling into table_a for the import

now i'm running into a problem where one id for table_a is returning 2 categories in table_b, this is problematic as i only want 1 of the values

i have worked out that i should use a priority field in table_b in which if table_b has 2 results for 1 id in table_a, i take value with the greatest priority (ie cat_a has a priority of 99 and cat b has a priority of 82, and cat_c has a priority of 95, cat_b will be chosen)

problem is that i'm not to sure how to do something like this or even if it's possible, anyone got some ideas

Upvotes: 0

Views: 263

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270643

When thinking about these problems, it is helpful to start with just the select statement:

select b.*
from table_b b join
     (select b.id, max(priority) as maxp
      from table_b
      group by b.id
     ) bmax
     on b.id = bmax.id and
        b.priority = bmax.maxp

Then you can substitute this into the update:

UPDATE table_a a join
       (select b.*
        from table_b b join
             (select b.id, max(priority) as maxp
              from table_b
              group by b.id
             ) bmax
             on b.id = bmax.id and
                b.priority = bmax.maxp
       ) b
       ON a.id = b.id
    SET a.category = b.category

Upvotes: 2

Related Questions