Update query

	Home Register Search Help Login

Sponsors

Shopping Cart Software
Ecommerce software integrated into Frontpage, Dreamweaver and Golive templates. No monthly fees and available in ASP and PHP versions.

Website Templates
We also have a wide selection of Dreamweaver, Expression Web and Frontpage templates as well as webmaster tools and CSS layouts.

Frontpage website templates
Creative Website Templates for FrontPage, Dreamweaver, Flash, SwishMax

Recent Posts

Todays Posts
Most Active posts
Posts since last visit
My Recent Posts
Mark posts read

Update query

View related threads: (in this forum | in all forums)

Logged in as: Guest

Users viewing this topic: none

Printable Version

All Forums >> Web Development >> ASP, PHP, and Database >> Update query

Page: [1]

SerenityNet

Posts: 1381
Joined: 6/12/2001
From: Allen, TX, USA
Status: offline

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

_____________________________

</Chaos, panic, & disorder - my work here is done.>


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	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 (in reply to SerenityNet)


SerenityNet Posts: 1381 Joined: 6/12/2001 From: Allen, TX, USA Status: offline	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 _____________________________ </Chaos, panic, & disorder - my work here is done.> (in reply to SerenityNet)


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	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 (in reply to SerenityNet)


SerenityNet Posts: 1381 Joined: 6/12/2001 From: Allen, TX, USA Status: offline	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 _____________________________ </Chaos, panic, & disorder - my work here is done.> (in reply to SerenityNet)


hhammash Posts: 1064 Joined: 8/19/2002 Status: offline	RE: Update query - 2/20/2003 15:53:50 Hi Andrew, Yes, your syntax is correct: " IF" " THEN" " ELSE" Regards Hisham (in reply to SerenityNet)


SerenityNet Posts: 1381 Joined: 6/12/2001 From: Allen, TX, USA Status: offline	RE: Update query - 2/20/2003 16:30:25 Thanks. Still, what is the first " I" in " IIf" ? Andrew _____________________________ </Chaos, panic, & disorder - my work here is done.> (in reply to SerenityNet)

Spooky

Posts: 26657
Joined: 11/11/1998
From: Middle Earth
Status: offline

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' " )

_____________________________

If you arent part of the solution, then there is good money to be made prolonging the problem

��k�

Database / DRW help Check out the Tutorials for Frontpage Databases

Forum FAQ' s

(in reply to SerenityNet)


SerenityNet Posts: 1381 Joined: 6/12/2001 From: Allen, TX, USA Status: offline	RE: Update query - 2/21/2003 13:19:27 Thank you. This will be handy. _____________________________ </Chaos, panic, & disorder - my work here is done.> (in reply to SerenityNet)