1

I want the table name which contain record when using union in mysql. I want to delete the records from that table or else i have to go through all tables and find the records and delete manually.

The query:

SELECT id FROM table1 WHERE cfid='2' 
UNION 
SELECT id FROM table2 WHERE ob_id='2' 
UNION 
SELECT id FROM table3 WHERE jid='2'
spencer7593
  • 106,611
  • 15
  • 112
  • 140
Amaan
  • 13
  • 5
  • Welcome to Stack Overflow. Please read [How do I ask a good question?](https://stackoverflow.com/help/how-to-ask) and [What topics can I ask about here?](https://stackoverflow.com/help/on-topic). Research, try something, add your code & point your problem. – pirho Dec 05 '17 at 06:37
  • well here is sample SELECT id FROM table1 WHERE cfid='2' UNION SELECT id FROM table2 WHERE ob_id='2' UNION SELECT id FROM table3 WHERE jid='2' now how i find which table contain the record. – Amaan Dec 05 '17 at 06:42

1 Answers1

5

Include a discriminator column in the individual SELECT statements, with a distinct hardcoded value in each. As an example, we add a column named src_ to each query ...

SELECT '1' AS src_, foo FROM bar WHERE ...

 UNION ALL

SELECT '2' AS src_, grumblestilt FROM skin WHERE ...

We can use the value returned in that column to determine which SELECT returned a particular row.

Note: if we don't need to eliminate duplicate rows, we can use the UNION ALL set operator. The UNION operator will go through the work of identifying rows that are duplicates, and eliminating duplicates so we get a result set containing unique tuples.

spencer7593
  • 106,611
  • 15
  • 112
  • 140