CODEWITHSUNDEEP
mysqlsql
fpdragon
fpdragon

Reputation: 1937

Replace several entries

I have two similar tables with the same columns and same type of data. The goal is to get the data of table_a but replace all entries with table_b values if there are value with the same id.

So I have the following two selects:

SELECT table_a.id, table_a.text

and

SELECT table_b.id, table_b.text

In pseudo code:

iterate table_a data
    if table_a.id == table_b.id then
        table_out.id = table_b.id
        table_out.text = table_b.text
    else
        table_out.id = table_a.id
        table_out.text = table_a.text
    end if
end iterate

The tables table_a and table_b data shall not be touched. Just the output of the select shall contain the data.

I was thinking first of joining the two tables with a left join. Then I would need a kind of if to switch columns for the select.

Thank you.

Upvotes: 0

Views: 45

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would recommend coalesce() for this purpose:

select a.id,
       coalesce(b.text, a.text) as text
from table_a a left join
     table_b b
     on a.id = b.id
order by a.id;

Upvotes: 2

Mangesh Auti
Mangesh Auti

Reputation: 1153

You can use LEFT JOIN it will give you all table_a record and common record between two tables. then use case logic to get require output

SELECT
t1.id,
(CASE WHEN t1.id=t2.id THEN t2.text ELSE t1.text END) AS text
FROM table_a t1
LEFT JOIN table_b t2
ON t1.id=t2.id
ORDER BY t1.id

DEMO

Upvotes: 1

Related Questions