<cfquery name="add" datasource="1avanco" dbtype="ODBC">
INSERT INTO Products2 (ProductID, Product, UnitPrice)
SELECT ProductID, Product, UnitPrice FROM new
WHERE new.Product NOT IN (SELECT Product from Products2)
</cfquery>
<cfquery name="update" datasource="1avanco" dbtype="ODBC">
UPDATE Products2 p, new n
SET p.UnitPrice = n.UnitPrice
WHERE p.Product = n.Product
</cfquery>
----- Original Message -----
From: "Britta Wingenroth" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, May 10, 2000 8:01 PM
Subject: 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
>
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META content=3D"text/html; charset=3Diso-8859-1" =
> http-equiv=3DContent-Type>
> <META content=3D"MSHTML 5.00.2920.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY bgColor=3D#ffffff>
> <DIV><FONT face=3DArial size=3D2>
> <DIV><FONT face=3DArial size=3D2>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? <BR><BR>I have two tables in my database, =
> "Products2"=20
> being the one to be updated,<BR>"new" being the one that the update =
> information=20
> is coming from. ***I have<BR>written the queries to express what I =
> want=20
> them to do, but they are not<BR>working.***<BR>I need to do two=20
> things:<BR><BR>TO ADD A NEW PRODUCT from "new" to "Products2" if not =
> already=20
> listed in<BR>"Products2":<BR><BR>INSERT INTO Products2 (ProductID, =
> Product,=20
> UnitPrice)<BR>SELECT ProductID, Product, UnitPrice FROM new<BR>WHERE =
> new.Product=20
> <>Products2.Product<BR>(but this creates huge amounts of =
> duplicates. =20
> I need the product just to be<BR>added once.)<BR><BR><BR>TO UPDATE A =
> PRODUCT=20
> from "new" to "Products2" if the product in "new" is the<BR>same as in=20
> "Products2" but the price in "new" has been updated.<BR><BR>Update price =
> of=20
> existing product:<BR>UPDATE Products2 (UnitPrice)<BR>SET =
> UnitPrice=3D(SELECT=20
> UnitPrice FROM new)<BR>WHERE new.Product=3DProducts2.Product<BR>(I want =
> to update=20
> those records in Products2 that have a different UnitPrice<BR>in =
> new. =20
> Similar problem to the above...)<BR><BR>Any=20
> ideas??<BR><BR>Britta<BR><BR><BR><BR><BR><BR></FONT></DIV></FONT></DIV></=
> BODY></HTML>
>
> ------=_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.
>
------------------------------------------------------------------------------
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.
