3 Replies Latest reply on Nov 17, 2006 11:33 AM by Dan Bracuk

    Update multiple records

    frankm30 Level 1
      I am trying to update mulitple records in a table, however I can't seem to figure out a way to differentiate the fields. I used a query to get all sales from a single customer using their customer number. I sometimes end up with 2 or more records. I want to mark both records as shipped (using a select statement with the choices of shipped and not shipped) then update these at the same time. However I have two values the field "shipping", one for each record, I also have two values for the field "salesnum" (the key for the sales table). If I just try to do a regular update query, then I only seem to update the last record with the last set of values.

      Anyhow, I have been althrough the forums and books to find a solution (not to mention tried several combinations of looping through the query). Any help will be appreciated. Thank you.

        • 1. Re: Update multiple records
          Dan Bracuk Level 5
          from a coding perspective, you don't even need the initial select query. Just write an update query and use the customer number in your where clause.

          from a more general perspective, what is your plan if the customer has two orders, but only one has been shipped?
          • 2. Re: Update multiple records
            frankm30 Level 1
            Why wouldn't I need the original query? I want to see what customer #2 ordered, then have the ability to update one or more fields in one or more of his sales records. The first thing to do would be to find out what the customer ordered.

            This is the crux of the problem, what if I want to update one record and leave the second, third, forth, etc alone because they haven't shipped yet?

            So I use my original query to pull all the sales records using where to filter everything but customer #2's records. I use a <cfoutput query ="getsales"> <tr>
            <td><select name="shipping">
            <option value ="#shipping#">#shipping#</option>
            <option value-"Shipped">Shipped</option>
            <option value="Not Shipped">Not Shipped</option></td>

            to display the records and set up the form for updating.

            The post method yields a value for "shipping" and "salesnum" that only seems to match the last value rendered by the query.

            Hope this clarifies things, thanks for the help.

            • 3. Re: Update multiple records
              Dan Bracuk Level 5
              You need to do this in two steps. Step 1 gets the unshipped orders and presents them to the user in a form. He picks the records to be updated and submits the form, You then update the records he selected.