Thanks for the prompt reply :)

When I executed the for loop, it left me at the prompt again saying that the was "No Active Transaction" and there was nothing for me to commit. Also I did search for the duplicates, and they were still there. I should have mentioned it, sorry.
--Sean

----- Original Message -----
From: Helen Borrie
To: firebird-support@yahoogroups.com
Sent: Monday, February 23, 2004 6:08 PM
Subject: Re: Fw: [firebird-support] newb: help with an update statement


At 05:38 PM 23/02/2004 -0600, you wrote:
>Hi,
>
> I have been trying to use the statement you gave me, but I keep coming up
>with this error:
>
> -803 Attempt to store duplicate value (visible to active transactions) in
>unique index "<string>" 335544349L
>
> After some google'n, I have come to realize that some of the parts were in
>the database twice. Once with the two leading chars, and once without. So
>am I right to assume this is the cause of this error?

Yes, since you have a unique index on the column.


> Since we only need one instance of the part, I decide to delete the
>duplicates and try again. However, when I try this statement it returns with
>no data instantly, and gives no error message:
>
> for
>
> select PARTNUM from PARTSTABLE where PARTNUM starting with 'MS'
>into :MYSTR
>
> do begin
>
> delete from ITEM where ITEMCODE=SUBSTRING(:MYSTR from 3 for 28);
>
> end

When you say "I tried this statement", I presume that you mean you wrote a
stored procedure with that statement in it?

A DELETE statement doesn't return any data. It just "does its thing" -
deletes rows. You'll only get "feedback" if it finds a row locked - in
which case the whole operation will fail and nothing happens.

Did you query the database after committing your EXECUTE PROCEDURE
statement, to discover whether any of the duplicates were still there?

you could do

select
p1.partnum from mytable p1
from partnum p1
where p1.partnum CONTAINING 'MS'
union
select p2.partnum from mytable p2
where p2partnum STARTING WITH 'MS'

order by 1


> I ever tried many variations on that (including some where I changed
>keywords to non-keywords and other invalid statements), and I still got no
>feedback what so ever. I have all permissions on the database. This should
>work, no?

I think you first need to find out if it is, in fact, NOT working.

>This is all in IBConsole, is there some better way to access the
>data?

Some would say "almost anything is better than IBConsole". But, for these
queries, it should be OK, as long as you ***commit*** the work and then
refresh your "checkup" query afterwards.

/heLen





------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]