Multiple UPDATEs in one CFQUERY?

[Marcus_in_Leeds]

I'm writing a system where people can update their entry in the database.

On the first page a form is populated with their data that they can then
change and click on submit.
On the second page I want to match the original data with the new data for
each form and only update the database with the fields that have changed.
Something along the lines of:

<cfquery name="insertintodata" datasource="nt2141-memberzone">
<cfif #form.newfield1# NEQ #data.FIELD1#>
UPDATE data
SET FIELD1 = '#form.newfield1#'
WHERE PVKEY = #Session.membernumber#</cfif>
<cfif #form.newfield2# NEQ #data.FIELD2#>
UPDATE data
SET FIELD1 = '#form.newfield2#'
WHERE PVKEY = #Session.membernumber#</cfif>
etc...
</cfquery>

It would appear, from the error message and my limited knowledge of SQL, that
this can't be done. Apart from having a CFQUERY for every CFIF, is there
another way to do this?

Thanks,
Mark

[Neculai Macarie]

Marcus_in_Leeds wrote:

> I'm writing a system where people can update their entry in the
> database.
>
> On the first page a form is populated with their data that they can
> then change and click on submit.
> On the second page I want to match the original data with the new
> data for each form and only update the database with the fields that
> have changed. Something along the lines of:
>
> <cfquery name="insertintodata" datasource="nt2141-memberzone">
> <cfif #form.newfield1# NEQ #data.FIELD1#>
> UPDATE data
> SET FIELD1 = '#form.newfield1#'
> WHERE PVKEY = #Session.membernumber#</cfif>
> <cfif #form.newfield2# NEQ #data.FIELD2#>
> UPDATE data
> SET FIELD1 = '#form.newfield2#'
> WHERE PVKEY = #Session.membernumber#</cfif>
> etc...
> </cfquery>
>
> It would appear, from the error message and my limited knowledge of
> SQL, that this can't be done. Apart from having a CFQUERY for every
> CFIF, is there another way to do this?

<cfquery name="getRecord">
SELECT *
FORM data
</cfquery>

<cfquery name="update">
UPDATE data
SET
PVKEY = #Session.membernumber#
<cfif form.newfield1 NEQ getRecord.FIELD1>, FIELD1 =
'#form.newfield1#'</cfif>
.........
WHERE PVKEY = #Session.membernumber#;
</cfquery>

--
<mack />

[supportcenter]

Why don't you just make a list of form fields to be checked and updated and
loop over that list doing the queries?

<cfset flist="">
<cfset flist = ListAppend(flist, "newfield1")>
...
<cfset flist = ListAppend(flist, "newfield10000")>
<cfloop list="#flist" index="idx">
<cfif ...>
<cfquery...>
...
</cfquery>
</cfif>
</cfloop>

[Dan Bracuk]

If it was me, I'd update every field. It's still only one record right?
There's a lot to be said for simplicity.

Originally posted by: Marcus_in_Leeds
I'm writing a system where people can update their entry in the database.

On the first page a form is populated with their data that they can then
change and click on submit.
On the second page I want to match the original data with the new data for
each form and only update the database with the fields that have changed.
Something along the lines of:

<cfquery name="insertintodata" datasource="membership">
<cfif #form.newfield1# NEQ #data.FIELD1#>
UPDATE data
SET FIELD1 = '#form.newfield1#'
WHERE PVKEY = #Session.membernumber#</cfif>
<cfif #form.newfield2# NEQ #data.FIELD2#>
UPDATE data
SET FIELD1 = '#form.newfield2#'
WHERE PVKEY = #Session.membernumber#</cfif>
etc...
</cfquery>

It would appear, from the error message and my limited knowledge of SQL, that
this can't be done. Apart from having a CFQUERY for every CFIF, is there
another way to do this?

Thanks,
Mark

[CF_Oracle]

Looks like typo in the second original Update query:
sjould it be FIELD2 not FIELD1?

If so then query will look like (I think Dan ment this too)

UPDATE data
SET
<cfif #form.newfield2# NEQ #data.FIELD2#>
FIELD1 = '#form.newfield1#',
</cfif>
<cfif #form.newfield2# NEQ #data.FIELD2#>
FIELD2 = '#form.newfield2#'
</cfif>
.....
WHERE PVKEY = #Session.membernumber#

[CF_Oracle]

I had typos too when copied rushing to help.
This is corrected code with addition of line if none of IF conditions met
UPDATE data
SET
FIELD1 = FIELD1
<cfif #form.newfield1# NEQ #data.FIELD1#>
FIELD1 = '#form.newfield1#',
</cfif>
<cfif #form.newfield2# NEQ #data.FIELD2#>
FIELD2 = '#form.newfield2#'
</cfif>

[Dan Bracuk]

No, I meant this

update thetable
set field1 = #form.field1#, field2 = #form.field2#, etc
where pvkey = #Session.membernumber#

Originally posted by: CF_Oracle
Looks like typo in the second original Update query:
sjould it be FIELD2 not FIELD1?

If so then query will look like (I think Dan ment this too)

UPDATE data
SET
<cfif #form.newfield2# NEQ #data.FIELD2#>
FIELD1 = '#form.newfield1#',
</cfif>
<cfif #form.newfield2# NEQ #data.FIELD2#>
FIELD2 = '#form.newfield2#'
</cfif>
.....
WHERE PVKEY = #Session.membernumber#

[CF_Oracle]

Dan, your query is the simpliest and would not heart if values of some fields or all not changed. It does not contradicts my code that I thought exactly what Marcus wanted.