0

I have two tables of the same database say D, and two tables are T1 and T2. T1 has attributes a1,a2...an and t2 has attributes a1,a2. I want a query that can update values of T1 with values of T2 only when T1.a1=T2.a1 and insert into T1 other wise. EXAMPLE:

enter image description here

enter image description here

Menelaos
  • 23,508
  • 18
  • 90
  • 155
Lucifer Harish
  • 3
  • 3

3 Answers3

0

Options

You have atleast three options:

  • Drop the mirror table, and run create as select to recreate the table
  • Use the mysql replace into syntax ( see: Replace Into Query Syntax )
  • Split your problem into two queries: One update and one insert query

Examples and details to follow...

Create as select

drop table t1;
create table t1 as select * from t2;

Replace into

Set t1.a1 and t2.a1 as primary keys on both tables. Run:

Replace into t1(a1,a2) select a1, a2 from t2;

Use an update and an insert:

UPDATE t1
INNER JOIN t2 ON t1.a1 = t2.a1
SET t1.a2 = t2.a2 where t1.a1 = t2.a1;

insert into t1(a1, a2) select a1, s2 from t2 where t2.a1 not in (select a1 from t1);
Menelaos
  • 23,508
  • 18
  • 90
  • 155
  • hey thanks for the answers but can you give the update query for update and insert – Lucifer Harish Jul 24 '17 at 15:05
  • Hey, again thanks. One last query that I have is if I delete a tuple from t2 that is not getting updated in t1. T1 still has the old t2 values. BTW this is only in the case of tuple deletion in T2. I am using the update and insert query – Lucifer Harish Jul 24 '17 at 15:31
  • `delete from t1 where not exists (select 1 from t2 where t2.a1 = t1.a1)` – Menelaos Jul 24 '17 at 15:41
0

You can try something like :

INSERT INTO T1 (you columns list) VALUES (here select your values from T2 with restriction) ON DUPLICATE KEY UPDATE `a2`=values(`a2`)
Omar EDDASSER
  • 153
  • 6
0

This is my answer from the way I understand the question. If this isn't what you were looking for, maybe you can clarify a bit. In the question a1 and a2 look like fields in the table. In your output expectation however, they look like values of another column. 

insert t1(a1,a2,a3)
select t2.a1,t2.a2,t2.a3 from t2
left join t1 on t1.a1 = t2.a2
where t1.a1 is null

update t1
set t1.a1 = t2.a1, t1.a2 = t2.a2, t1.a3 = t2.a3
from t1 inner join t2 on t1.a1 = t2.a2

/*
    table data before record changes
    t1
    a1         a2         a3
    ---------- ---------- ----------
    bar        foo        fizz
    yin        yang       zen
    vidi       vini       vici
    lug        lag        lugger
    dig        dug        digger
    zoom       zimm       zammy
    riki       tiki       tavi
    yuz        wuz        wiz
    bip        bap        boop
    zip        zap        zing
    ding       aling      aling


    t2
    a1         a2         a3
    ---------- ---------- ----------
    foo        bar        fizz
    yuz        wuz        wiz
    vini       vidi       vici
    bip        bap        boop
    zip        zap        zing
    ding       aling      aling
    zimm       zoom       zumm



    t1 data after record changes
    t1
    a1         a2         a3
    ---------- ---------- ----------
    foo        bar        fizz
    yin        yang       zen
    vini       vidi       vici
    lug        lag        lugger
    dig        dug        digger
    zimm       zoom       zumm
    riki       tiki       tavi
    yuz        wuz        wiz
    bip        bap        boop
    zip        zap        zing
    ding       aling      aling
    yuz        wuz        wiz
    bip        bap        boop
    zip        zap        zing
    ding       aling      aling

*/
Eric Smith
  • 79
  • 1
  • 6