Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9

    Mass-update table data with a single query?


    I have a table that has various rows of data grabbed from a table in my DB. 3 of the columns display the queried data. The last column, however, contains text fields for the user to hand-enter values. When the user hits the Submit button, all of the values entered in these text fields should be saved to another table.

    How do I do this mass-update using one query? I have tried the below code, but for some reason (that I cannot figure out), the cfloop doesn't seem to work.
    Code:
    <cfif isDefined("FormSubmitted")>   <!---If form is submitted--->
        <cfloop query="getIDs" startrow="1" endrow="#getIDs.RecordCount#">
    	<cfquery name="UpdateCalculations" datasource="myDB">
    	    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('#getIDs.ID#', '#getIDs.Name#', #FORM.txtValue#)
    	</cfquery>
       </cfloop>
    </cfif>
    'getIDs' is the query that works on another table (a different one from the one being updated) to retrieve all of the IDs+Names presently in the DB. What I cannot figure out is what is the best way to insert all the text field values using just one query! (Just FYI, the values entered in the text boxes are numbers)

    Also, could someone please explain to me why the <cfloop> inside of the <cfif> doesn't work?
    I am a CF-SQL newbie; any help is appreciated!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    349
    Rep Power
    0
    instead try this,

    Code:
    insert into myTable (id, name, age)
    select id, name, txtValue
    from myOtherTable
    why is age = txtValue ?

    Comments on this post

    • sql_n00b agrees
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    Originally Posted by Master__Chief
    instead try this,

    Code:
    insert into myTable (id, name, age)
    select id, name, txtValue
    from myOtherTable
    why is age = txtValue ?
    txtValue is what the user enters in the textboxes on each row of the table. I have used 'age' only as an example variable.
    Again, the ID and Name columns in the HTML table are queried from the DB. The age column is where the textboxes are located and where the user hand-enters numbers.
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,690
    Rep Power
    4288
    which database system? DB2? Sybase? Informix? MySQL?

    i'm gonna guess MySQL because you seem to want to put single quotes around the ID value, and ID columns are usually numeric

    you're missing one of those single quotes --
    Code:
    VALUES	('#getIDs.ID#, '#getIDs.Name#', #FORM.txtValue#)
    your CFLOOP is probably working, and your INSERT is failing
    Last edited by r937; August 6th, 2009 at 05:32 AM.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    Originally Posted by r937
    which database system? DB2? Sybase? Informix? MySQL?

    i'm gonna guess MySQL because you seem to want to put single quotes around the ID value, and ID columns are usually numeric

    you're missing one of those single quotes --
    Code:
    VALUES	('#getIDs.ID#, '#getIDs.Name#', #FORM.txtValue#)
    your CFLOOP is probably working, and your INSERT is failing
    Sorry, I should have mentioned I am using MS-SQL 2005 in my original post. Also, the IDs are not pure numbers;they are alpha-numeric, for ex. 0BH01. Each of the 5 characters in the ID stands for something and hence the need for non-numeric IDs. But that's a different story.

    Anyway, when I hard-code the values in my SQL INSERT statement, the values are stored in the DB as expected. It's the loop that does not seem to work. Any idea why?

    P.S. Sorry about the missing quote. I have fixed the original post now.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,690
    Rep Power
    4288
    Originally Posted by sql_n00b
    It's the loop that does not seem to work. Any idea why?
    could we see the getIDs query?

    Comments on this post

    • sql_n00b agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    349
    Rep Power
    0
    Code:
    <cfif isDefined("FormSubmitted")>   <!---If form is submitted--->
        <cfloop query="getIDs" startrow="1" endrow="#getIDs.RecordCount#">
    	<cfquery name="UpdateCalculations" datasource="myDB">
    	    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('#getIDs.ID#', '#getIDs.Name#', #FORM.txtValue#)
    	</cfquery>
       </cfloop>
    </cfif>
    the first line would probably be :Form.Submitted instead of FormSubmitted,

    second, right after cfif isDefined, do a cfdump.

    third, enable debugging and/or use:

    Code:
    <cftry>...
    <cfcatch>
    <cfdump var="#cfcatch#">
    </cfcatch>
    </cftry>
    to see if the statement is causing any errors.

    finally, make sure that #getIDs.RecordCount# returns some number gt 1
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    Originally Posted by r937
    could we see the getIDs query?
    Here it is!
    Code:
    <cfquery name="getIDs" datasource="#request.dsn#">
       SELECT DISTINCT ID, Name
       FROM    tblUserData
    </cfquery>
    I am quite confident the getIDs query is fine because that is the query I am using to populate the table with the Name and ID#.
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,690
    Rep Power
    4288
    Originally Posted by sql_n00b
    ... because that is the query I am using to populate the table with the Name and ID#.
    which table?

    you probably don't need the DISTINCT, unless each name and ID pair occurs multiple times in the tblUserData table table

    by the way, if you're looping over all rows, you can simply say <CFLOOP QUERY="queryname"> without the start and end parameters
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    @Master__Chief:
    'cfdump'ing the getIDs query shows that it does contain the required data.

    @r937:
    I have got rid of the start and end row attributes in the cfloop now
    and the DISTINCT keyword.

    I scratched around the code a bit more and the error I get now is:
    "There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement."

    Something's not right with either the INSERT statement or the cfloop. I cannot get it to insert multiple rows of data in one shot! If I have, for example, 3 rows to insert, then what's happening is all of the 3 text-field values are clubbed into 1 SQL statement (values being separated by commas). So like this:
    Code:
    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('0BH01', 'Mickey Mouse', 5,4,8)
    Instead what I want is this:
    Code:
    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('0BH01', 'Mickey Mouse', 5)
    Code:
    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('0BH02', 'Minnie Mouse', 4)
    Code:
    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('0BH03', 'Donald Duck', 8)
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    349
    Rep Power
    0
    The error means that whaever values you are providing are less or more than the table fields in the insert:

    Code:
    insert into my table (field1, field2, field3)
    values ('a','b')
    or

    Code:
    insert into my table (field1, field2, field3)
    values ('a','b','c','d')

    what happened to your txtValue ?

    SELECT DISTINCT ID, Name
    FROM tblUserData
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    To add to my earlier post:
    The INSERT works fine if there is only one row to insert. More than one row, and I am in trouble!!
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2005
    Posts
    349
    Rep Power
    0
    The problem is FORM.txtValue is a list of values.

    you need to try this:

    Code:
    <cfloop query="getIDs" startrow="1" endrow="#getIDs.RecordCount#">
    	<cfquery name="UpdateCalculations" datasource="myDB">
    	    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('#getIDs.ID#', '#getIDs.Name#', #ListGetAt(FORM.txtValue, getIDs.CurrentRow)#)
    	</cfquery>
       </cfloop>
    This will work as long as your Len(form.txtValue) = getIDs.recordCount
  26. #14
  27. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,690
    Rep Power
    4288
    Originally Posted by sql_n00b
    ... all of the 3 text-field values are clubbed into 1 SQL statement (values being separated by commas). So like this:
    Code:
    INSERT INTO myTable (ID, Name, Age)
    	    VALUES	('0BH01', 'Mickey Mouse', 5,4,8)
    what this means is that your #FORM.txtValue# variable contains multiple values

    does your form actually have multiple form fields all with the same name?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Posts
    45
    Rep Power
    9
    Master__Chief,

    #ListGetAt(FORM.txtValue, getIDs.CurrentRow)#

    did the trick for me!

    Just FYI (and to answer r937's query) Yes, I have only text box defined in the form. How many times the text box needs to be shown in the HTML form is based on the 'getIDs.RecordCount' value.

    r937 and Master__Chief, thanks to both of you for your time and patience with me
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo