Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 4 of 4

Thread: SQL Query

  1. #1
    chrismartz is offline Senior Member
    Join Date
    Apr 2010
    Posts
    339

    Default SQL Query

    I'm wondering if anyone has any ideas of what to change in my query here. Currently it doesn't update any rows. Any ideas?

    Code:
    UPDATE gc SET gc.Balance = agc.S6CRBL - o.OrderTotal FROM GiftCard gc INNER JOIN @APLUSGiftCards agc ON gc.SerialNumber = agc.S6CRNO LEFT JOIN Orders o ON gc.SerialNumber = o.CouponCode WHERE gc.SerialNumber IN (SELECT SerialNumber FROM @WebGiftCards) AND o.CouponType = @CouponType AND ((o.PaymentMethod = 'PURCHASEORDER' AND o.TransactionState = 'PENDING') OR (o.PaymentMethod = 'CREDITCARD' AND o.TransactionState = 'AUTHORIZED'))
    Do I need to loop to do the update instead?

  2. #2
    mmcgeachy is offline Senior Member
    Join Date
    Sep 2008
    Posts
    174

    Default

    Where is the APLUSGiftCards table coming from? I don't recall seeing that in the out of the box code and I'm not having much luck finding it.

  3. #3
    chrismartz is offline Senior Member
    Join Date
    Apr 2010
    Posts
    339

    Default

    Sorry about that. This is part of a sproc. @APLUSGiftCards and @WebGiftCards are temp tables that I load with data on exec. @CouponType = 2

  4. #4
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    It's not updating because your WHERE and JOINs are not returning any rows to be affected. The only way we can work it out is, if you supply the structure of your temp tables and some example data.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!