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 !!

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

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 !

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

Thank You so much Willy !