Results 1 to 7 of 7
  1. #1
    Affiliate Manager Allen Nance's Avatar
    Join Date
    January 18th, 2005
    Location
    Colorado River, Bullhead City AZ
    Posts
    1,604
    Hi this is Allen the Joat'mon.

    Ive gotten my database up and running.. so here's my question(s)

    i load a temp table with new data

    how do I update the real data by querying the product id or SKU?

    I want to only update the record's price.
    I want to delete the sku's not found
    I want to add any sku's that are new.....

    I have been pulling me eyes out trying to find this on the net... closest I can come to talks about UPDATE, SET and WHERE...

    but how to put it together in a statement....

    Thanks in advance for the hep.... ( that's southern )

    Poopie

    Jack of all trades, master of none

  2. #2
    Member
    Join Date
    January 18th, 2005
    Posts
    95
    UPDATE tablename
    SET (fieldname1 = 'data_to_use',
    fieldname2 = 'moredata',
    numerical_field3 = 1234)
    WHERE uniqueID = 9876

    edited to make more sense

  3. #3
    Affiliate Manager Allen Nance's Avatar
    Join Date
    January 18th, 2005
    Location
    Colorado River, Bullhead City AZ
    Posts
    1,604
    Ross, Thanks.. I just want to verify before I do something stupid..

    here's what I have...

    The master table has 14,000 products... one of the fields include a unique link number. So the first record is linkid 1. This has to be the same all the time, otherwise the product get's lost.

    Other fields are things like sku, description, price etc. There are some other fields that put in click counts, votes, reviews etc.

    Now: I have created a duplicate table called update with identical fields. I can populate the new feed into it. (I hope)

    What I want to do is to match the sku in the update table and update the master table with it's data. But not all fields. Mainly the price column. While keeping all the other columns with the same information.

    so I guess I want to update the price in master table based on a querie of the sku which is identical in both tables.

    Sheesh.. am I rambling?

    Thanks in advance

    Allen
    Signup Now for our KiteandWind affiliate program exclusivly at Shareasale.
    * 7% + Bonus - 365 return days
    * 2nd Tier Signup Bonus - Parasite Free - Auto Deposit Merchant
    * Free Datafeed - PopShops- Performance Cash Bonus

  4. #4
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    I'm not sure this is what you want but I'll give it a go -

    <?php

    //start a query on the update table
    $sql="SELECT * FROM updatetable";
    $query=mysql_query($sql);

    //pick out individual linkids and match with master table and update price

    while ($fetch=mysql_fetch_array($query))
    {
    $linkid=$fetch['linkid'];
    $newprice=$fetch['price'];

    $sqlupdate="UPDATE mastertable SET price='".$newprice."' WHERE linkid='$linkid'";
    $queryupdate=mysql_query($sqlupdate);

    }

    ?>

    Did this really quick so haven't worked out if syntax is right - should do.

    Oscar
    My DataFeed Scripts - php datafeed scripts for your site
    Shareasale datafeed scripts - to display Shareasale datafeeds
    Linkshare datafeed scripts - for multiple Linkshare merchants

  5. #5
    Member
    Join Date
    January 18th, 2005
    Posts
    95
    Same solution, different language (Cold Fusion)

    <!--- first get the new table information --->

    <CFQUERY name="duplicatetablequery" datasource="yourdatasourcename">
    SELECT * FROM duplicatetable
    </CFQUERY>

    <!--- now loop through new table data and insert into original table --->

    <CFLOOP query="duplicatetablequery">

    <CFQUERY name="update_master_table" datasource="yourdatasourcename">

    UPDATE mastertable
    SET (price = 12.34)
    WHERE mastertable.sku = #duplicatetablequery.sku#

    </CFQUERY>
    </CFLOOP>

  6. #6
    Member
    Join Date
    January 18th, 2005
    Posts
    95
    By the way Oscar, PHP sucks, Cold Fusion rocks, as our two examples here prove



    hehehe

    Of course, PHP is free and Cold Fusion is not...and more web hosts offer PHP than offer CF...

  7. #7
    Animal Lover
    Join Date
    January 18th, 2005
    Location
    oz
    Posts
    1,210
    <BLOCKQUOTE class="ip-ubbcode-quote"><font size="-1">quote:</font><HR> By the way Oscar, PHP sucks, Cold Fusion rocks, as our two examples here prove <HR></BLOCKQUOTE>

    Haven't looked at CF but may take it up after I've tackled xml feeds.

    Oscar
    My DataFeed Scripts - php datafeed scripts for your site
    Shareasale datafeed scripts - to display Shareasale datafeeds
    Linkshare datafeed scripts - for multiple Linkshare merchants

  8. Newsletter Signup

+ Reply to Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •