Just noting for myself on how to update tables from other tables…

Say you have a table called “customers” and you have a customerID as the primary key…

you now have a cross reference table that joins customers to certain products, it’s only a 1 to 1 relationship in our situation (not very real world with this example for customers/products, but it’s only an example), the database was made in flaw, so you want to do some clean up…

Your goal is to get rid of the cross reference table called “tblCustomerProduct” it has three columns, “customerID, productID, catID” you want to add the column “productID, and catID” to the “customers” table, and want to get rid of the cross reference table…

now you run your update, and it’s a simple sql statement rather than looping over it in coldfusion the db runs it in no time!

--psuedo code
update c
SET c.productID = o.productID,
c.catID = o.catID
FROM customers
JOIN tblCustomerProduct o
ON c.customerID = o.customerID

works good…

again this is just psuedo code and is a reminder to myself to remember how to do this simple task, I would have done in coldfusion before, now i can use this 😉