Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2012
    Posts
    3

    Red face How to use Minus operation in Sybase

    Hi,

    I have table 1 as T1 and its backup copy as T2, both having same structure, in oracle its easy to do a minus operation by select * from T1 minus select * from T2 to get the differences in both the tables

    but i tried hard to find same operation in Sybase as sybase doesnt supports minus operation.

    Could anyone suggests how should i see the difference between both the tables in sybase, its quite urgent...

    Thanks
    Aman

  2. #2
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    513
    You'll need a primary key/unique identifier (i.e. ID):

    select * from T1 where ID not in (select ID from T2)

    This displays all records from T1 that have an ID that's not present in T2.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    Dec 2012
    Posts
    3
    I have this kind of query where

    select col1, col2 from T1 where
    not exists
    (select 1 from T2 where T1.col1=T2.col2 and T1.col1=T2.cpl2)

    but this works only in case of tables having few columns but not in case of large tables having more columns.

  4. #4
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    513
    What is the purpose of T2? Is it an unaltered copy of T1, but perhaps with fewer records? Or is the data in T2 altered?
    If it's the first, you can use my first example. You'll only need to compare unique identifiers because the other columns haven't changed.

    If you're dealing with situation 2, you will have to compare every column, which is quite an endeavor. And it will probably wreak havoc on your performance.

    Also: why do you want to compare the two tables?
    I'm not crazy, I'm an aeroplane!

  5. #5
    Join Date
    Dec 2012
    Posts
    3
    T1 is the altered table having new records while the T2 table is the baselined table, actually this I want to do for Regression where I want to know if any new records added due to program run so thats why i want to do minus operation.

  6. #6
    Join Date
    Jan 2004
    Location
    The Hague/Utrecht, NL
    Posts
    513
    In that case I think you will have to create giant where-clause, compairing every column...
    I'm not crazy, I'm an aeroplane!

  7. #7
    Join Date
    Mar 2008
    Posts
    96
    There is another way to get the difference of the tables' content.
    Take the BCP out of both the tables in files T1.txt and T2.txt
    and u can find the difference b/w them using diff command of unix.
    ie on UNIX prompt execute diff T1 T2

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
  •