Updating multiple records in a linked table simultaneously

[Aaron Roberson]

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

[Dan Bracuk]

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

[Aaron Roberson]

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