Set tablename to each row

Question

I selected data from multiple table with query:

SELECT * FROM TABLE1 
UNION ALL 
SELECT * FROM TABLE2

When I deleting data I want to see to which table the row is belonging to. I want to know is there a way to set table name to each row while retrieving data. I dont want to add new column with table_name to a table. Any Idea?

score 1 · Answer 1 · answered Sep 18 '21 at 07:02

1

Add a column with the name of the table:

SELECT 'TABLE1', TABLE1.* FROM TABLE1 
UNION ALL 
SELECT 'TABLE2', TABLE2.* FROM TABLE2

answered Sep 18 '21 at 07:02

Luuk

12,245
5
22
33

lol I feel so stupid) Thank you. I Idk how I forget to do this – DorFik Sep 18 '21 at 07:18

score 1 · Accepted Answer · answered Sep 18 '21 at 07:06

1

SELECT *, "table1" as tablename FROM TABLE1 
UNION ALL 
SELECT *, "table2" FROM TABLE2

answered Sep 18 '21 at 07:06

buniek

36
1

Ashiqur Rahman · Answer 3 · 2021-09-19T17:56:44.247

1

     SELECT t1.*, t1.data FROM TABLE1 t1
         UNION 
         SELECT t2.*, t2.data FROM TABLE2 t2   
    
> If you want to get without duplicate data from two tables then you use UNION


    SELECT t1.*, t1.data FROM TABLE1 t1
        UNION ALL 
        SELECT t2.*, t2.data FROM TABLE2 t2

> If you want to get all data from two tables then you use UNION ALL

edited Sep 19 '21 at 17:56

answered Sep 18 '21 at 07:29

Ashiqur Rahman

36
7

Set tablename to each row

3 Answers3