Advanced queries on MySQL

1. Update the data from one Table to another Table

Let's say we have Table1 and Table2 as the following:

Table1
col_num---- col_alpha-----col_cap
1-----------------a---------------NULL
2-----------------b---------------NULL
3-----------------c---------------NULL

Table2
col_alpha---- col_cap
a-----------------A
b-----------------B
c-----------------C

Let's say you want to populate Table1.col_cap (reads as col_cap column of Table1) with the data of Table2.col_cap if Table1.col_alpha=Table2.col_alpha, you can run the following query in MySQL

UPDATE Table1 JOIN Table2 ON Table2.col_alpha=Table1.col_alpha SET Table1.col_cap=Table2.col_cap

Final Result:
Table1
col_num---- col_alpha-----col_cap
1-----------------a---------------A
2-----------------b---------------B
3-----------------c---------------C


2. Append the data from one table to the another table

Let's consider that we have above two tables: Table1 and Table2

We can use the INSERT and SELECT query to append the data.

INSERT INTO Table1 (`col_alpha`,`col_cap`) SELECT `col_alpha`,`col_cap` FROM Table2;

Final Result:
Table1
col_num---- col_alpha-----col_cap
1-----------------a---------------A
2-----------------b---------------B
3-----------------c---------------C
4-----------------a---------------A
5-----------------b---------------B
6-----------------c---------------C

3. Select the data from multiple tables

Let's consider that we have above two tables: Table1 and Table2

Say you want to view Table1.col_num and Table2.col_cap if Table1.col_alpha=Table2.col_alpha, then

SELECT Table1.col_num,Table2.col_cap FROM Table1,Table2 WHERE Table1.col_alpha=Table2.col_alpha;

Final Result: View

col_num-------------col_cap
1-------------------------A
2-------------------------B
3-------------------------C