CODEWITHSUNDEEP
mysqlinner-join
Brianne Alexander
Brianne Alexander

Reputation: 139

MYSQL Update one table with the values other table qualified using 2 columns

I have been trying to get this query to work and hoping someone can give me some advice.

I have two tables. master and aux. I need master to be updated with any rows that are in aux and not in master based on groupid and section id.

I need to actually do this with multiple aux tables, but as long as I can figure out how to do it on one, I can just manually run the query on each table I need.

So far I've just been trying to use a select to get the data from aux that should be inserted into master. Once I have that I can modify it to create the insert query. (I hope)

select 
   a.id 
from 
   master as m, 
   aux as a
where 
   a.gid = m.groupid 
and 
   a.sid = m.sectionid 
and 
   not in m.groupid 
and 
   not in m.sectionid

This query is not working :(

master table

id    groupid    sectionid
1     A Group    21
2     A Group    23
3     B Group    55
4     C Group    999
5     D Group    52A
6     D Group    53

aux table

id    gid        sid
1     A Group    21
2     A Group    22
3     A Group    23
4     B Group    55
5     B Group    55A
6     C Group    999
7     D Group    52A
8     D Group    53
9     D Group    56

master table after query

id    groupid    sectionid
1     A Group    21
2     A Group    23
3     B Group    55
4     C Group    999
5     D Group    52A
6     D Group    53
7     A Group    22
8     B Group    55A
9     D Group    56

Thank you in advance for any help.

Upvotes: 4

Views: 71

Answers (2)

Tech Savant
Tech Savant

Reputation: 3766

Can you try this...

select 
    id 
from 
    aux as a
where 
    not exists (
        select 
            id 
        from 
            master 
        where 
            groupid = a.gid 
        and 
            sectionid = a.sectionid
    )

SQLFiddle

Upvotes: 0

anthonybell
anthonybell

Reputation: 5998

I don't think and not in m.groupid is valid sql. A not exist with a sub-select should do the trick:

insert into master (groupid, sectionid)
select a.gid, a.sid
from aux as a
where not exists(
  select *
  from master as m
  where m.groupid = a.gid
  and a.sid = m.sectionid
)

Upvotes: 4

Related Questions