Re: SQL or PHP with CF for this problem?
Britta, I don't think PHP or mySQL would help your situation since the problem is strictly database related and mySQL does not support nested queries which is helpful at times. > TO ADD A NEW PRODUCT from "new" to "Products2" if not already listed in > "Products2": > INSERT INTO Products2 (ProductID, Product, UnitPrice) > SELECT ProductID, Product, UnitPrice FROM new > WHERE new.Product <>Products2.Product > (but this creates huge amounts of duplicates. I need the product just = > to be > added once.) Try this: INSERT INTO Products2 (ProductID, Product, UnitPrice) SELECT ProductID, Product, UnitPrice FROM new WHERE ProductID NOT IN (SELECT ProductID FROM Products2) This will get all all the products NOT IN the Products2 table. > Update price of existing product: > UPDATE Products2 (UnitPrice) > SET UnitPrice=3D(SELECT UnitPrice FROM new) > WHERE new.Product=3DProducts2.Product > (I want to update those records in Products2 that have a different = > UnitPrice "SELECT UnitPrice FROM new" will get ALL the prices from that table and that's not what you want. You only want to return the record for that specific ProductID (WHERE New.ProductID = Products2.ProductID). UPDATE Products2 SET UnitPrice= (SELECT UnitPrice FROM new WHERE New.ProductID = Products2.ProductID) WHERE new.ProductID = Products2.ProductID AND New.Price <> Products2.Price Xing -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SQL or PHP with CF for this problem?
This is a multi-part message in MIME format. --=_NextPart_000_0014_01BFBAB6.4D0B3EA0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've used plain SQL so far with CF. To solve this problem it was = suggested to me to use PHP/MySql. I'm very anxious NOT to have to learn = another new language just for this if I don't have to . any = suggestions, please? =20 I have two tables in my database, "Products2" being the one to be = updated, "new" being the one that the update information is coming from. ***I = have written the queries to express what I want them to do, but they are not working.*** I need to do two things: TO ADD A NEW PRODUCT from "new" to "Products2" if not already listed in "Products2": INSERT INTO Products2 (ProductID, Product, UnitPrice) SELECT ProductID, Product, UnitPrice FROM new WHERE new.Product <>Products2.Product (but this creates huge amounts of duplicates. I need the product just = to be added once.) TO UPDATE A PRODUCT from "new" to "Products2" if the product in "new" is = the same as in "Products2" but the price in "new" has been updated. Update price of existing product: UPDATE Products2 (UnitPrice) SET UnitPrice=3D(SELECT UnitPrice FROM new) WHERE new.Product=3DProducts2.Product (I want to update those records in Products2 that have a different = UnitPrice in new. Similar problem to the above...) Any ideas?? Britta --=_NextPart_000_0014_01BFBAB6.4D0B3EA0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've used plain SQL so far with = CF. To solve=20 this problem it was suggested to me to use PHP/MySql. I'm very = anxious NOT=20 to have to learn another new language just for this if I don't have to = . any=20 suggestions, please? I have two tables in my database, = "Products2"=20 being the one to be updated,"new" being the one that the update = information=20 is coming from. ***I havewritten the queries to express what I = want=20 them to do, but they are notworking.***I need to do two=20 things:TO ADD A NEW PRODUCT from "new" to "Products2" if not = already=20 listed in"Products2":INSERT INTO Products2 (ProductID, = Product,=20 UnitPrice)SELECT ProductID, Product, UnitPrice FROM newWHERE = new.Product=20 <>Products2.Product(but this creates huge amounts of = duplicates. =20 I need the product just to beadded once.)TO UPDATE A = PRODUCT=20 from "new" to "Products2" if the product in "new" is thesame as in=20 "Products2" but the price in "new" has been updated.Update price = of=20 existing product:UPDATE Products2 (UnitPrice)SET = UnitPrice=3D(SELECT=20 UnitPrice FROM new)WHERE new.Product=3DProducts2.Product(I want = to update=20 those records in Products2 that have a different UnitPricein = new. =20 Similar problem to the above...)Any=20 ideas??Britta --=_NextPart_000_0014_01BFBAB6.4D0B3EA0-- -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
SQL or PHP with CF for this problem?
This is a multi-part message in MIME format. --=_NextPart_000_0107_01BFBABA.98D543E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've used plain SQL so far with CF. To solve this problem it was = suggested to me to use PHP/MySql. I'm very anxious NOT to have to learn = another new language just for this if I don't have to . any = suggestions, please? =20 I have two tables in my database, "Products2" being the one to be = updated, "new" being the one that the update information is coming from. ***I = have written the queries to express what I want them to do, but they are not working.*** I need to do two things: TO ADD A NEW PRODUCT from "new" to "Products2" if not already listed in "Products2": INSERT INTO Products2 (ProductID, Product, UnitPrice) SELECT ProductID, Product, UnitPrice FROM new WHERE new.Product <>Products2.Product (but this creates huge amounts of duplicates. I need the product just = to be added once.) TO UPDATE A PRODUCT from "new" to "Products2" if the product in "new" is = the same as in "Products2" but the price in "new" has been updated. Update price of existing product: UPDATE Products2 (UnitPrice) SET UnitPrice=3D(SELECT UnitPrice FROM new) WHERE new.Product=3DProducts2.Product (I want to update those records in Products2 that have a different = UnitPrice in new. Similar problem to the above...) Any ideas?? Britta --=_NextPart_000_0107_01BFBABA.98D543E0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I've used plain SQL so far with = CF. To solve=20 this problem it was suggested to me to use PHP/MySql. I'm very = anxious NOT=20 to have to learn another new language just for this if I don't have to = . any=20 suggestions, please? I have two tables in my database, = "Products2"=20 being the one to be updated,"new" being the one that the update = information=20 is coming from. ***I havewritten the queries to express what I = want=20 them to do, but they are notworking.***I need to do two=20 things:TO ADD A NEW PRODUCT from "new" to "Products2" if not = already=20 listed in"Products2":INSERT INTO Products2 (ProductID, = Product,=20 UnitPrice)SELECT ProductID, Product, UnitPrice FROM newWHERE = new.Product=20 <>Products2.Product(but this creates huge amounts of = duplicates. =20 I need the product just to beadded once.)TO UPDATE A = PRODUCT=20 from "new" to "Products2" if the product in "new" is thesame as in=20 "Products2" but the price in "new" has been updated.Update price = of=20 existing product:UPDATE Products2 (UnitPrice)SET = UnitPrice=3D(SELECT=20 UnitPrice FROM new)WHERE new.Product=3DProducts2.Product(I want = to update=20 those records in Products2 that have a different UnitPricein = new. =20 Similar problem to the above...)Any=20 ideas??Britta --=_NextPart_000_0107_01BFBABA.98D543E0-- -- Archives: http://www.eGroups.com/list/cf-talk To Unsubscribe visit http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
