CODEWITHSUNDEEP
mysqlunion
Daniel Pilch
Daniel Pilch

Reputation: 2257

Merging mysql data from two tables into one dataset and updating

I have two identical tables with different names which I would like to merge into one useable array dataset using some sort of query. I've heard the UNION function is good for this however I have never used it before.

OK so heres some pseudo table code:

TABLE_1 & TABLE_2

The query I would use to get data from both tables would be:

SELECT col_1, col_2 FROM TABLE_ WHERE ...

╔═════════╦═══════════╗
║ col_1   ║   col_2   ║
╠═════════╬═══════════╣
║      37 ║         1 ║
║      37 ║         2 ║
║     ... ║       ... ║
╚═════════╩═══════════╝

How could I combine the data from both tables to get one dataset? I would also like to update records in the dataset but how could I do this if I don't know what table the data originated from?

Thanks in advance.

Upvotes: 1

Views: 2844

Answers (1)

Digital Chris
Digital Chris

Reputation: 6202

Use UNION:

(SELECT col_1, col_2, 'TABLE_1' AS original_table FROM TABLE_1 LIMIT 0,250)
UNION
(SELECT col_1, col_2, 'TABLE_2' AS original_table FROM TABLE_2 LIMIT 0,250)

Further info:

UNION ALL would also be an option which would give you duplicate identical rows (based on the data you supply, it seems you would not need this).

Updating of records would simply use where col_1 = {value} and col_2 = {value2}... doesn't matter which table was the originator.

edit: here it is working http://sqlfiddle.com/#!2/71a88/1

Upvotes: 3

Related Questions