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?

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

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? This is all in IBConsole, is there some better way to access the
data?

TIA,

--Sean

> ----- Original Message -----
> From: "Helen Borrie" <helebor@...>
> Newsgroups: egroups.ib-support
> To: <firebird-support@yahoogroups.com>
> Sent: Friday, February 20, 2004 4:03 PM
> Subject: Re: [firebird-support] newb: help with an update statement
>
>
> > At 02:00 PM 20/02/2004 -0600, you wrote:
> > >Hi,
> > >We have been using a front end for all our production management, and

we

> > >have since decided to change the format of our part numbers. The

software

> we
> > >have runs off of Firebird, and since the software offers no utilities

to

> do
> > >what I need (at least with out manually changing them all) I belive the
> > >easiest way to make the changes is just to do them with IBConsole.
> > >Currently our parts are in the form of two letters, followed by the
> actual
> > >part number ("AA######...." ). What I need to do is parse out the first
> two
> > >letters of the part number field and update it, leaving only the

numbers.

> As
> > >I see it from reading the Language Reference, I would need to use the
> substr
> > >funtion to do that acutal parsing and the update command to store it

back

> > >in. I have a some what limited knowledge of SQL, having only

experienced

> > >MySQL in small, limited, web site implemetations. Knowing that, this is
> how
> > >I decided to go about it:
> > >
> > > update PARTSTABLE set PARTNUM=substr(PARTNUM,2,strlen(PARTNUM)-1)
> where
> > >PARTNUM like 'AA%';
> > >
> > >When I ran that in IBConsole, I got a "variable undefined" error for

the

> > >reference to "PARTNUM" that was the first argument in "substr." When I
> > >looked at the docs for the update command I saw this:
> > > Notes on the UPDATE statement
> > >
> > > In SQL and isql, you cannot use val as a parameter placeholder

(like

> > >"?").
> > >
> > > In DSQL and isql, val cannot be a variable.
> > >
> > >Since I can't use variables inside this type of statement, I am at a

loss

> as
> > >to how I should go about this. Any help will be appreciated.
> >
> > Use the internal SUBSTRING function. Now, let's assume PARTNUM is a
> > varchar(20) - change your numbers to suit.
> >
> > update PARTSTABLE
> > set PARTNUM = SUBSTRING(PARTNUM FROM 3 FOR 18)
> > where PARTNUM STARTING WITH 'AA';
> >
> > Note: In addition to the IB 6 LangRef, you also need the release

notes!!

> >
> > /heLen
>