Forum Stats

  • 3,770,444 Users
  • 2,253,115 Discussions
  • 7,875,457 Comments

Discussions

Sequence without using sequence number

There is a table called table_1 as below

Table_1

ID, seq_no, name, address

1   1            'A'             'B'

1   2            'A'             'B'

2   1            'B'             'B'

3   1            'C'             'B'

3   2            'D'             'B'

4   5            'D'             'B'

I'm trying to insert as below but giving unique constraint error as i've unique index on ID,SEQ_NO,NAME

select max(seq_no)+1 from table_1 where id =3) is taking same value if there are more than one row in select statement and i cannot use sequence also, can anybody help

?

insert into table_1(ID, seq_no, name, address)

select 6, (select max(seq_no)+1 from table_1 where id =3), name, address from table_1 where id =3;