Will CFCATCH/CFTRY with DATABASE error be a better solution?
i.e - TRY TO UPDATE no matter what -> if error occurs (row not exists) ->
INSERT.
Is this better then SELECT?
:)
----- Original Message -----
From: "Phil Ewington" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, April 22, 2001 4:29 PM
Subject: RE: UPDATE / INSERT
> OK, u.recordcount could be 0 if the record you are expecting to find does
> not exist or if the value of DT in that record does not match your
criteria.
> Your code will create a new record if either of the above is true. If this
> is what you want I can't really see a better way of doing it.
>
> Phil.
>
> > -----Original Message-----
> > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > Sent: 22 April 2001 15:51
> > To: CF-Talk
> > Subject: Re: UPDATE / INSERT
> >
> >
> > something like this.
> >
> > Thanks!
> >
> > <cfquery name="u">
> > SELECT DN from logs WHERE DT=#DT#</cfquery>
> >
> > <cfif u.recordcount gt 0>
> >
> > <cfquery>
> > UPDATE logs
> > SET DN=DN+#DN# WHERE DT=#DT#</cfquery>
> >
> > <cfelse>
> >
> > <cfquery>
> > INSERT INTO logs (DN,DT) VALUES (#DN#,#DT#)</cfquery>
> >
> > </cfif>
> >
> >
> > ----- Original Message -----
> > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > To: "CF-Talk" <[EMAIL PROTECTED]>
> > Sent: Sunday, April 22, 2001 3:20 PM
> > Subject: RE: UPDATE / INSERT
> >
> >
> > > Please post your current SELECT/UPDATE code so I can see
> > exactly what you
> > > are doing.
> > >
> > >
> > > Phil.
> > >
> > > > -----Original Message-----
> > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > Sent: 22 April 2001 15:30
> > > > To: CF-Talk
> > > > Subject: Re: UPDATE / INSERT
> > > >
> > > >
> > > > It'll only work if I have all the possible values already placed
> > > > in COLUM1.
> > > >
> > > > ----- Original Message -----
> > > > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > > Sent: Sunday, April 22, 2001 2:34 PM
> > > > Subject: RE: UPDATE / INSERT
> > > >
> > > >
> > > > > You need to set your table to have COLUM1 have a value 0 assigned
by
> > > > > default, this is possible in SQL not sure about other dbs. When a
> > record
> > > > is
> > > > > created, COLUM1 will be automatically assigned a value of 0 by
> > default.
> > > > So,
> > > > > if you always do an update, the value will be updated even if
> > > > you have not
> > > > > previously assigned a value. Does this make sense?
> > > > >
> > > > >
> > > > > Phil.
> > > > >
> > > > > > -----Original Message-----
> > > > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > > > Sent: 22 April 2001 13:59
> > > > > > To: CF-Talk
> > > > > > Subject: Re: UPDATE / INSERT
> > > > > >
> > > > > >
> > > > > > but if the value is not exists in "COLUM" I'll need to use
INSERT
> > > > > > to assign
> > > > > > default values.
> > > > > > I can't predicte all the possible values, so I can't make a
> > > > > > "READY-FOR-UPDATE-ONLY" table.
> > > > > >
> > > > > >
> > > > > > ----- Original Message -----
> > > > > > From: "Phil Ewington" <[EMAIL PROTECTED]>
> > > > > > To: "CF-Talk" <[EMAIL PROTECTED]>
> > > > > > Sent: Sunday, April 22, 2001 1:18 PM
> > > > > > Subject: RE: UPDATE / INSERT
> > > > > >
> > > > > >
> > > > > > > Micheal,
> > > > > > >
> > > > > > > Thinking about it, if the value does exist and you
> > simply want to
> > > > check
> > > > > > what
> > > > > > > that value is before updating, you could always do an update
> > > > > > irrespective
> > > > > > of
> > > > > > > the value, sure, this will mean doing an un-necessary update
> > > > sometimes,
> > > > > > but
> > > > > > > isn't that better than making two connections (select &
> > > > update) if the
> > > > > > > update is required. Only a thought.
> > > > > > >
> > > > > > >
> > > > > > > Phil.
> > > > > > >
> > > > > > > > -----Original Message-----
> > > > > > > > From: Phil Ewington [mailto:[EMAIL PROTECTED]]
> > > > > > > > Sent: 22 April 2001 12:04
> > > > > > > > To: CF-Talk
> > > > > > > > Subject: RE: UPDATE / INSERT
> > > > > > > >
> > > > > > > >
> > > > > > > > Do you mean if the record exists UPDATE, or if the
> > value in the
> > > > column
> > > > > > is
> > > > > > > > specified update?
> > > > > > > >
> > > > > > > > If the record does not exist, use cftry/cfcatch: try UPDATE,
> > > > > > if fails do
> > > > > > > > INSERT.
> > > > > > > >
> > > > > > > > If the record exists but value is NULL, you are
> > probably better
> > > > > > > > off setting
> > > > > > > > the columns default value to zero, this way you will
> > > > always be able
> > > > to
> > > > > > > > increment the value by 1 using UPDATE.
> > > > > > > >
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Phil.
> > > > > > > >
> > > > > > > > > -----Original Message-----
> > > > > > > > > From: Michael Lugassy [mailto:[EMAIL PROTECTED]]
> > > > > > > > > Sent: 22 April 2001 13:06
> > > > > > > > > To: CF-Talk
> > > > > > > > > Subject: UPDATE / INSERT
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > I'm trying to find the best way to have SQL do this:
> > > > > > > > > IF COLUM1 already contains "VALUE" then UPDATE COLUM2+1
> > > > > > > > > IF NOT, INSERT COLUM1,COLUM2 VALUES ("VALUE",1)
> > > > > > > > >
> > > > > > > > > Currently I'm using SELECT / RecordCount to first find
> > > > out if the
> > > > > > > > > VALUE exists.
> > > > > > > > >
> > > > > > > > > There must be a better way...
> > > > > > > > >
> > > > > > > > > Michael.
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > > >
> > > > > > > >
> > > > > > >
> > > > > >
> > > > >
> > > >
> > >
> >
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
