Newbie question: duplicate values

shamusom · #1 (**permalink**) 03-30-11, 16:29

Using sqlite3, how do you search across multiple tables (>2) for a possible duplicate value that might exist in the same column (same column name exists in each table)

Is there a way to do this using sqlite3? How do you select all the tables and perform this query?

shammat · #2 (**permalink**) 03-30-11, 16:34

I don't know if SQLite supports UNIONs but this is at lest standard ANSI SQL:

Code:

SELECT the_column, count(*)
FROM ( 
   SELECT the_column
   FROM table_1
   
   UNION ALL

   SELECT the_column
   FROM table_2

   UNION ALL
   
   SELECT the_column
   FROM table_3
) t
WHERE the_column = 'your_search_value'
GROUP BY the_column
HAVING count(*) > 1

shamusom · #3 (**permalink**) 03-30-11, 16:42

Thanks for that tidbit, I have something similar, but how do I select all the tables with a given name without having to type all of them in one by one? Know what I mean? All the tables start with a certain prefix, and I want to search all of those. Possible?

thx for the info.

stolze · #4 (**permalink**) 04-01-11, 02:14

You could generate the SQL query by scanning the catalog, e.g.:

Code:

SELECT table_name
FROM    information_schema.tables
WHERE  table_name like '<your-prefix>%'

and then build the above query programmatically. With a bit of recursive SQL you can build the whole query in a single SQL statement, fetch that result and execute it dynamically as another query.

p.s: The ISO standard defines the catalog tables in a schema named INFORMATION_SCHEMA. Most systems use other schema names and may also have other table/column names.

Internet Services

Web Development

Digital Marketing

Consumer Tech