Updating a table with YES and NO in a field

  • I have an  issue just brought to my attention.  If the apdelay = 0 then No needs to be put into the delay field

    UPDATE tbl_salesactivity sn

    INNER JOIN apj aj

    ON sn.shipment = aj.shipmentid

    AND aj.APDelay > '0'

    SET sn.delay = 'Yes'

     

    I rewrote it with a case statement. It is really taking a long time since there is only 140000 records in tbl_salesactivity.

    UPDATE tbl_salesactivity sn

    LEFT JOIN arh ar ON sn.shipment = ar.shipment_id

    SET sn.delay = CASE WHEN ar.ardelay > 0 THEN 'Yes' ELSE 'No' END;

  • You could have 2 different UPDATES, or have 1 update and use a CASE statement in your SET statement for the different conditions.

  • UPDATE sn
    SET
    sn.delay = CASE
    WHEN ar.ardelay > 0
    THEN 'Yes'
    ELSE 'No'
    END
    FROM tbl_salesactivity sn
    LEFT JOIN arh ar
    ON sn.shipment = ar.shipment_id;

    I assume you have a proper indexes on the tables to support the join.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply