CODEWITHSUNDEEP
mysql
met00
met00

Reputation: 57

SQL Query to find missing records between two tables and then update the second with the missing records from the first

Two tables. 8 fields in each. Both tables have the same data, one with 137,002 record (tablea) and one with 135,759 records (tableb). Both tables share a common primary field if three columns (qid, sid, aid).

Is there a single query that will. 1) compare tablea to tableb on the primary field and 2) if the record is in tablea and not tableb copy the record from tablea to tableb

I would rather be able to update tableb with an sql query rather than writing a php loop to go through the 137,002 and do a compare on each one.

Thanks

Upvotes: 1

Views: 1830

Answers (4)

met00
met00

Reputation: 57

So, the following worked.

insert into f_step_ans (`qid`, `assid`, `sid`, `sas`, `cas`, `etim`, `stim`, `endtim`, `fil`)
select  
    t1.qid,
    t1.assid,
    t1.sid,
    t1.sas,
    t1.cas,
    t1.etim,
    t1.stim,
    t1.endtim,
    t1.fil
from f_step_ans_back t1
where 
    not exists (select t2.qid, t2.sid,t2.assid from f_step_ans as t2 where t2.qid = t1.qid and t2.assid = t1.assid and t2.sid = t1.sid)

1,588 records were moved from the f_step_ans_back table (old backup table) to the f_step_ans table (partially recovered backup + new data). Reporting shows that everything is working like it should be. Thank you all for the help.

Upvotes: 0

Ashutosh Arya
Ashutosh Arya

Reputation: 1168

Use merge...and use insert only....not update.

Upvotes: 0

Alex Pliutau
Alex Pliutau

Reputation: 21947

INSERT INTO tableb AS b
(SELECT * FROM tablea AS a WHERE NOT EXISTS (SELECT * FROM tableb AS b2 WHERE b2.id = a.id))

Upvotes: 0

Sergio
Sergio

Reputation: 6948

That should be smth looking like:

insert into table2 (qid, sid ...)
    select  
        t1.qid,
        t1.sid,
        ...
    from table1 t1
    where 
        not exist (select t2.qid, t2.sid, ... from table2 t2 where t2.qid = t1.qid and t2.sid = t1.sid...)

Upvotes: 1

Related Questions