MySQL How can I select data from two tables so that rows from the second one overwrote those from the first one?

Question

The first table:

day | title
1   | "Lorem"
2   | "Ipsum"

The second table:

day | title
2   | "Amor"
3   | "Zeus"

Now I need such a result:

day | title
1   | "Lorem"
2   | "Amor"
3   | "Zeus"

How to do that with a single query?

Edit:

"Zeus" added to the second table.

http://superuser.com/questions/90301/sync-two-mysql-databases — Sathish D, Sep 17 '13 at 13:27
What should the result be if second table has `3 | Zeus` row? — Glavić, Sep 17 '13 at 13:34
Then my predictions were correct; I have posted SQL bellow, and test on SQLfiddle. — Glavić, Sep 17 '13 at 13:39
Why are people actually upvoting this question? It's very unclear as to what exactly is being asked. First of all he didn't specify what would happe when T2 contained days that weren't in T1 (fixed now thanks to the comment), second, it's still unclear to me whether he wants to **retrieve** data or **overwrite** it in T1 — Kippie, Sep 17 '13 at 13:39

score 2 · Accepted Answer · answered Sep 17 '13 at 13:32

http://sqlfiddle.com/#!2/0dc08/1

SELECT t1.Day, COALESCE(t2.Word, t1.Word) AS Word
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.Day = t1.Day
UNION ALL
SELECT t2.Day, t2.Word
FROM Table2 t2
LEFT JOIN Table1 t1 ON t1.Day = t2.Day
WHERE t1.Day IS NULL

The code above prefers t2 to t1 when a day is found in both tables. And the UNION ALL at the bottom gets all records that are JUST in t2. If that case can't happen, then remove the UNION ALL statement and below.

manji · Answer 2 · 2013-09-17T13:37:13.343

1

SELECT t1.day, COALESCE(t2.title, t1.title) title
  FROM t1 LEFT JOIN t2 on t1.day = t2.day

Edit:

Following @Glavić comment, here a solution where there is data in t2 not in t1 that should be considered:

SELECT t1.day, COALESCE(t2.title, t1.title) title
  FROM t1 LEFT JOIN t2 on t1.day = t2.day
UNION ALL
SELECT t2.day, t2.title
  FROM t2 LEFT JOIN t1 on t2.day = t1.day
WHERE t1.day IS NULL

edited Sep 17 '13 at 13:37

answered Sep 17 '13 at 13:25

manji

47,442
5
96
103

That would only return 'Amor' because there would be no JOIN from t2 – Eli Gassert Sep 17 '13 at 13:26
I forgot the 'left' in 'left join'. It does not deserve -1 I think. – manji Sep 17 '13 at 13:27
It does if "forgetting" makes the answer completely wrong. As you've fixed the problem, I've removed the downvote. – Eli Gassert Sep 17 '13 at 13:30
@Glavić that depends on what the asker actually wants. If he only expects results from T2 to return when they also occur in T1 it's a correct answer. – Kippie Sep 17 '13 at 13:33
@EliGassert, sure but I was fixing it at the same as you writing your comment. – manji Sep 17 '13 at 13:37
@manji I don't want to start a flame war here. It's simple: if the answer is wrong, it gets a downvote because it's detrimental to readers. The fact that you fixed it means I "fixed" my downvote. It's not personal. I have no way of knowing you were in the process of fixing something. SO doesn't show that information so I can somehow wait to vote until you're done with all your edits. Your answer now looks nothing like the first version, and there's no way for me to have known this was how it was going to end up based on the first version. – Eli Gassert Sep 17 '13 at 13:39

score 0 · Answer 3 · answered Sep 17 '13 at 13:38

0

SELECT *
FROM (
    SELECT * FROM t2
    UNION
    SELECT * FROM t1
) AS tX
GROUP BY day

Test it on sqlfiddle.

answered Sep 17 '13 at 13:38

Glavić

42,781
13
77
107

I was thinking of going this route as well. I was curious what the implications of a UNION would be over a JOIN and UNION ALL as in my answer. I was concerned that a UNION would eat cycles as it checks for duplicates and figured it might be slower than two JOIN'd queries. Thoughts? – Eli Gassert Sep 17 '13 at 13:41
1

@Eli Gassert: Of course your UNION ALL is more efficient because it avoids an unneccessary DISTINCT. But more important: above query will never work on any other DBMS and it might fail on MySQL if they change the internal implementation: there's no guarantee that the result of a UNION will be ordered in any specific way without ORDER BY. – dnoeth Sep 17 '13 at 14:18

score 0 · Answer 4 · answered Sep 17 '13 at 14:35

In addition to Eli Gassert's solution, avoids joining both tables two times (and runs on each DBMS, too)

SELECT day,
   COALESCE(MAX(CASE WHEN x = 2 THEN title END),
            MAX(CASE WHEN x = 1 THEN title END))
FROM (
    SELECT day, title, 1 AS x FROM t1
    UNION ALL
    SELECT day, title, 2 AS x FROM t2
) AS tX
GROUP BY day;

And if MySQL supported FULL join it would be much easier:

SELECT
   COALESCE(t1.day, t2.day),
   COALESCE(t2.titl, t1.titl)
FROM t1 FULL JOIN t2 ON t1.day = t2.day;

score -1 · Answer 5 · answered Sep 17 '13 at 13:25

-1

Using GROUP BY day should do the trick.

answered Sep 17 '13 at 13:25

Darren Crabb

570
2
10

I'm presuming here that the OP is asking for how to retrieve the data from two tables, rather than insert. – Darren Crabb Sep 17 '13 at 13:30

score -1 · Answer 6 · edited May 23 '17 at 10:25

-1

Use INSERT ON DUPLICATE.

Check this answer for example

That is assuming You have UNIQUE index on day column.

edited May 23 '17 at 10:25

Community

1
1

answered Sep 17 '13 at 13:28

Gustek

3,680
2
22
36

I think the user is asking for a ``SELECT`` query, not a way to update his first table. – Kippie Sep 17 '13 at 13:30
In title he wrote, `merge` and `overwrote`. – Gustek Sep 17 '13 at 13:36

score -1 · Answer 7 · answered Sep 17 '13 at 13:41

-1

Here's another way -

select a.day, case when a.title <> b.title then b.title else a.title end title
from table1 a
left join table2 b
on a.day = b.day

answered Sep 17 '13 at 13:41

Ace Amr

108
5

MySQL How can I select data from two tables so that rows from the second one overwrote those from the first one?

7 Answers7

Edit: