Update query (Full Version)

All Forums >> [Web Development] >> ASP, PHP, and Database



Message

SerenityNet -> Update query (2/19/2003 19:01:43)

In an Access database, I have a quintillion records. In one column, I have fields where some of the data ends in the letter " T" and some does not. I want to get rid of the " T" , but leave everything else intact. It would look something like this:

To Start: 
Column 1     Column 2     Column 3
item1        another1     again1
item2T       another2     again2
item11       another11    again11
item3T       another3     again3
item9T       another9     again9


When Done: 
Column 1     Column 2     Column 3
item1        another1     again1
item2        another2     again2
item11       another11    again11
item3        another3     again3
item9        another9     again9


How do I do this?

Thanks in advance,
Andrew



hhammash -> RE: Update query (2/19/2003 21:38:19)

Hi,

Try this:

UPDATE Table1 SET Table1.ItemName = IIf(Right([ItemName],1)=" t" ,Left([ItemName],Len([ItemName])-1),[ItemName]);

Here ItemName is your Column1.

Regards
Hisham



SerenityNet -> RE: Update query (2/20/2003 13:32:34)

Thanks Hisham,

When I tried:
UPDATE [WC 01] SET [WC 01].[Mrkt Prt Id] = If(Right([Mrkt Prt Id],1)=" t" ,Left([Mrkt Prt Id],Len([Mrkt Prt Id])-1),[Mrkt Prt Id]);
then I received the error " Undefined function ' If' in expression."

However, I tried
UPDATE [WC 01] SET [WC 01].[Mrkt Prt Id] = Left([Mrkt Prt Id],Len([Mrkt Prt Id])-1)
WHERE Right([Mrkt Prt Id],1)=" t" ;
and this seems to work fine.

Could there be something I' m missing here?

Thanks,
Andrew



hhammash -> RE: Update query (2/20/2003 13:39:04)

Hi Andrew,

It should be IIF not If.

I posted it after trying it on my database, it should work.

Anyway, your version should be working. It is correct.

Regards
Hisham



SerenityNet -> RE: Update query (2/20/2003 13:59:54)

Yep, that worked fine. Thank you. I thought the IIF was a typo' in your original line. What is the " I" in " IIF" ?

Am I correct that the syntax means:
= IIf( the IF part , the THEN part , the ELSE part );

Andrew



hhammash -> RE: Update query (2/20/2003 15:53:50)

Hi Andrew,

Yes, your syntax is correct:

" IF" " THEN" " ELSE"

Regards
Hisham



SerenityNet -> RE: Update query (2/20/2003 16:30:25)

Thanks.

Still, what is the first " I" in " IIf" ?

Andrew



Spooky -> RE: Update query (2/20/2003 22:48:10)

Its called " Immediate" IF or IIF
Its quite a cool function in SQL and is unfortunately missing in ASP.

An example in asp is : 

Function IIF(sInput,strue,sfalse)
  If sInput then
    IIF = strue
  Else
    IIF = sfalse
  End if
End Function


Then, in asp code you can call IIF as a function 

' Test Variable
Variable=True

Response.write IIF(Variable, " This will write ' True' " , " This will write ' False' " )



SerenityNet -> RE: Update query (2/21/2003 13:19:27)

Thank you.

This will be handy.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125