Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Bothell, WA
    Posts
    3

    Unanswered: Anything in sybase similar to rowid in Oracle

    Hi,
    Can anyone tell me if there is something similar to rowid (oracle) in Sybase, Cos I need to delete the duplicate rows from a table . I need a simpler way to do it.

    I do not want to do the following

    Select distinct * from <tablename> into <temptable>

    Truncate table <tablename>

    Insert into <tablename> Select * from <temptable>

    Anything simpler than this would be of a great help !

    Thanks !!

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya mmhaise,

    Here's one way,

    1> create table t1 (c1 int, c2 char(10))
    2> go
    1> create table t2 (c3 int, c4 char(10))
    2> go
    1> create unique index pk on t2 (c3, c4) with ignore_dup_key
    2> go
    1> insert into t1 (1, "Hello")
    2> go 10
    (1 row affected)
    10 xacts:
    1> insert t2 select * from t1
    2> go
    Duplicate key was ignored.
    (1 row affected)

    Cheers
    Willy

  3. #3
    Join Date
    Dec 2002
    Location
    Bothell, WA
    Posts
    3
    Hi Willy
    Is this the only way to do , cos I have the table already created , can I use ignore_dup_key option on the existing table with alter table command. I have worked in Oracle and am very much new to Sybase .
    Thanks so much for the response !

  4. #4
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    You cannot use the ignore_dup_key on an already duplicate row table.

    Okay...Here's Take2:

    1> create table t1 (c1 int, c2 char(10))
    2> go
    1> insert into t1 values (1,"Hello")
    2> go 10
    (1 row affected)
    10 xacts:
    1> insert into t1 values (2, "Gidday")
    2> go
    (1 row affected)
    1> select * into t2
    2> from t1
    3> where c1 = (select c1
    4> from t1
    5> group by c1
    6> having count(*) > 1)
    7> go
    (10 rows affected)
    1> delete from t1
    2> where c1 = (select c1
    3> from t1
    4> group by c1
    5>having count(*) > 1)
    6> go
    (10 rows affected)

    1> select * from t1
    2> UNION ALL
    3> select * from t2
    4> go
    c1 c2
    ----------- ----------
    2 Gidday
    1 Hello

    (2 rows affected)

    Cheers
    Willy
    Last edited by willy_and_the_ci; 12-29-02 at 04:34.

  5. #5
    Join Date
    Dec 2002
    Location
    Bothell, WA
    Posts
    3
    Thank You so much Willy !





    Originally posted by willy_and_the_ci
    You cannot use the ignore_dup_key on an already duplicate row table.

    Okay...Here's Take2:

    1> create table t1 (c1 int, c2 char(10))
    2> go
    1> insert into t1 values (1,"Hello")
    2> go 10
    (1 row affected)
    10 xacts:
    1> insert into t1 values (2, "Gidday")
    2> go
    (1 row affected)
    1> select * into t2
    2> from t1
    3> where c1 = (select c1
    4> from t1
    5> group by c1
    6> having count(*) > 1)
    7> go
    (10 rows affected)
    1> delete from t1
    2> where c1 = (select c1
    3> from t1
    4> group by c1
    5>having count(*) > 1)
    6> go
    (10 rows affected)

    1> select * from t1
    2> UNION ALL
    3> select * from t2
    4> go
    c1 c2
    ----------- ----------
    2 Gidday
    1 Hello

    (2 rows affected)

    Cheers
    Willy

Posting Permissions

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