Professional Web Applications Themes

Updating multiple records in a linked table simultaneously - Coldfusion Database Access

I am working on an e-commerce application and I need help with updating the product details for a single product with multiple formats. --DB STRUCTURE--- Products table: tbl_Products fld_prodID (PK, autonum) fld_prodTitle fld_prodDesc Linking Table: tbl_Prod_details fld_prodID (FK) fld_formatID (FK) Product Formats table: tbl_ProdFormat fld_formatID (PK, autonum) fld_formatTitle If I wanted to update this product and select multiple formats, would I have to build two update queries or could I do it with one? What would the SQL look like? Thanks for your help and suggestions! -Aaron...

  1. #1

    Default Updating multiple records in a linked table simultaneously

    I am working on an e-commerce application and I need help with
    updating the product details for a single product with multiple
    formats.

    --DB STRUCTURE---

    Products table:

    tbl_Products

    fld_prodID (PK, autonum)
    fld_prodTitle
    fld_prodDesc

    Linking Table:

    tbl_Prod_details

    fld_prodID (FK)
    fld_formatID (FK)

    Product Formats table:

    tbl_ProdFormat

    fld_formatID (PK, autonum)
    fld_formatTitle

    If I wanted to update this product and select multiple formats, would
    I have to build two update queries or could I do it with one? What
    would the SQL look like?

    Thanks for your help and suggestions!
    -Aaron
    Aaron Roberson Guest

  2. #2

    Default Re: Updating multiple records in a linked tablesimultaneously

    If the only thing you are doing is giving a product more formats, the answer
    might be that you don't run any update queries. You run insert queries into
    your tbl_Prod_details table.

    If you are changing a format, you do something like

    update tbl_Prod_details
    set fld_formatID = theNewNumber
    where fld_prodID = somenumber
    and fld_formatID = theOldNumber


    Dan Bracuk Guest

  3. #3

    Default Re: Updating multiple records in a linked table simultaneously

    Dan Bracuk wrote:
    > If the only thing you are doing is giving a product more formats, the answer
    > might be that you don't run any update queries. You run insert queries into
    > your tbl_Prod_details table.
    >
    > If you are changing a format, you do something like
    >
    > update tbl_Prod_details
    > set fld_formatID = theNewNumber
    > where fld_prodID = somenumber
    > and fld_formatID = theOldNumber
    >
    >
    In my case, I am updating/inserting multiple formats at once. Therefore,
    the value for "theOldNumber" in the WHERE clause would be more than 1
    number.

    Perhaps I could do something like the following:

    <cfquery name="qFormats" dsn="#application.dsn#">
    SELECT fld_format_ID
    FROM tblprodFormats
    WHERE fld_format_ID = #newID#
    </cfquery>

    <cfif qFormats.recordcount > 0>
    <cfquery name="updateFormats" dsn="#application.dsn#>
    update tbl_Prod_details
    set fld_formatID = #fID#
    where fld_prodID = #pID#
    and fld_formatID = #newID#
    </cfquery>
    <cfelse>
    insertquery
    </cfif>

    However, this would not solve my problem of inserting or updating more
    than one record in the formats table simultaneously.

    Any more help would be greatly appreciated at this point!

    -Aaron
    Aaron Roberson Guest

Similar Threads

  1. Updating Multiple records fields in a database atonce
    By kris f in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: February 17th, 03:36 AM
  2. Updating Multiple Database Rows Simultaneously
    By scott in forum PHP Development
    Replies: 5
    Last Post: October 12th, 02:27 PM
  3. updating multiple records on one page
    By BP Prgm in forum ASP Database
    Replies: 1
    Last Post: September 17th, 02:52 AM
  4. updating multiple records via online form
    By dukeofwhelmsley in forum ASP
    Replies: 2
    Last Post: September 8th, 07:36 PM
  5. Updating Multiple Records
    By Jordan Marton in forum Macromedia Dreamweaver
    Replies: 5
    Last Post: July 10th, 08:40 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139