Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Newbie question: duplicate values

    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?

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    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

  3. #3
    Join Date
    Mar 2011
    Posts
    2

    duplicate values

    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •