query help..

[techker]

Hey guys im updating a database every day with a script i did that check's a DB file and changes the prices in the pricing DB according to the file..

 

now the issue is when the product is not in the DB file it puts the prices at 0.00$ in the priciing DB..

 

is there a way to update all the products in the DB file and keep the others at the price it is already set in the pricing DB??

 

this is the query:

$sql2 = "UPDATE xcart_pricing p
LEFT JOIN TbItemInventory pp
ON p.productid = pp.XcartID
SET p.price = pp.SalesPrice2
WHERE p.productid =  pp.XcartID";
$retval = mysql_query( $sql2 );
if(! $retval )
{
  die('Could not update data: ' . mysql_error());
}
echo "Updated data successfully ";

 

TblInventory is a OBDC file uploaded from an access DB file every night(DBSync for MS Access & MySQL really cool app converts access to mysql and uploads it.)

 

the file is basically the POS system

 

So it uploads to the DB then i compare the xcart pricing DB to the TBLInventory DB ..

 

am i clear?lol

[Muddy_Funster]

have you tried running it as an INNER JOIN?

[techker]

no?but im shure it would to the same no?

[Muddy_Funster]

maybe not, LEFT and RIGHT joins include null value matches from one table or another...INNER joins only affect fields that match on both sides of the join.

[techker]

i don't see how i can make this in a inner join?

 

SELECT * FROM TbItemInventory INNER JOIN xcart_pricing ON productid =XcartID;

[Muddy_Funster]

change the word LEFT in the original query to INNER.  Your select query should look like

SELECT * FROM TbItemInventory INNER JOIN xcart_pricing ON TbItemInventory.XcartID = xcart_pricing.productid ;

[fenway]

Well, a NULL into a non-null decimal field would return 0.00.

[techker]

change the word LEFT in the original query to INNER.  Your select query should look like

SELECT * FROM TbItemInventory INNER JOIN xcart_pricing ON TbItemInventory.XcartID = xcart_pricing.productid ;

 

so this should update only the products selected and not put 0.00 on the others..

[techker]

ok so this a select..i need update?

 

EDIT..i mest up wrong file..

 

this is my query to update the prices..

 

$sql4 = "UPDATE xcart_pricing p
LEFT JOIN TbItemInventory pp
ON p.productid  = pp.XcartID
SET p.price = pp.SalesPrice
WHERE p.membershipid =  1 ";
$retval4 = mysql_query( $sql4 );
if(! $retval4 )
{
  die('Could not update data: ' . mysql_error());
}


////6



$sql5 = "UPDATE xcart_pricing p
LEFT JOIN TbItemInventory pp
ON p.productid  = pp.XcartID
SET p.price = pp.SalesPrice2
WHERE p.membershipid =  5 ";

[Muddy_Funster]

change the word LEFT in the original query to INNER.  Your select query should look like

SELECT * FROM TbItemInventory INNER JOIN xcart_pricing ON TbItemInventory.XcartID = xcart_pricing.productid ;

"UPDATE xcart_pricing p
INNER JOIN TbItemInventory pp
ON p.productid  = pp.XcartID
SET p.price = pp.SalesPrice
WHERE p.membershipid =  1 "

[techker]

so i can understand the inner join will only join the both databse with the same info and not bother the other information?

[fenway]

INNER JOIN requires both sides of your ON clause to match.