Updating a COALESCE

Posted on 2009-02-09
Last Modified: 2012-05-06
I have a query below that all i want to do is update the c_tracking_no

The problem is that to get the c_tracking_no from the query  it uses

COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no)  

Is it possible to update this
SELECT     oe_pick_ticket.pick_ticket_no, oe_pick_ticket.print_date, COALESCE (p21_view_clippership_return_10004.carrier_name, AS c_carrier, 

                      COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no) AS c_tracking_no, 

                      COALESCE (p21_view_clippership_return_10004.total_charge, oe_pick_ticket.freight_out) AS c_freight_out, oe_pick_ticket.freight_in, 

                      oe_pick_ticket.pick_and_hold, COALESCE (p21_view_clippership_return_10004.shipped_date, oe_pick_ticket.ship_date) AS c_ship_date, 

                      oe_pick_ticket.invoice_no, oe_pick_ticket.auxiliary, oe_pick_ticket.direct_shipment, oe_pick_ticket.delete_flag, oe_pick_ticket.order_no, 

                      oe_pick_ticket.total_tax, freight_code.freight_cd, oe_pick_ticket.freight_code_uid, oe_hdr.packing_basis, oe_hdr.company_id, 

                      oe_pick_ticket.oe_pick_ticket_type_cd, oe_pick_ticket.outgoing_freight_cost, 

                      CASE WHEN oe_pick_ticket_type_cd = 1921 THEN 'Parts Pick Ticket' WHEN oe_pick_ticket_type_cd = 1922 THEN 'Service Pick Ticket' WHEN oe_pick_ticket.direct_shipment

                       = 'Y' THEN 'Direct ptshipment' WHEN oe_pick_ticket.auxiliary = 'Y' THEN 'Auxiliary Pick Ticket' WHEN oe_pick_ticket.pick_and_hold = 'Y' THEN CASE WHEN

                       oe_hdr.packing_basis = 'Tag and Hold' THEN 'Tag And Hold' ELSE 'Pick And Hold' END ELSE CASE ptshipment.transaction_type_cd WHEN 222 THEN 'Shipped via Order Entry'

                       ELSE 'Regular Pick Ticket' END END + CASE WHEN COALESCE (p21_view_clippership_return_10004.clippership_return_uid, 0) 

                      <> 0 THEN ' (Clippership)' WHEN COALESCE (ptshipment.shipment_uid, 0) 

                      <> 0 THEN ' (' + carriertype.code_description + ')' ELSE '' END AS shipment_type_display, 

                      CASE ptshipment.transaction_type_cd WHEN 222 THEN oe_hdr.order_no ELSE oe_pick_ticket.pick_ticket_no END AS transaction_no, 

                      ptshipment.shipment_uid, p21_view_clippership_return_10004.clippership_return_uid, address.carrier_type_cd, 

                      COALESCE (ptshipment.ship_location_id, oe_pick_ticket.location_id) AS Expr1, ptshipment.transaction_type_cd

FROM         oe_pick_ticket INNER JOIN

                      oe_hdr ON oe_hdr.order_no = oe_pick_ticket.order_no LEFT OUTER JOIN

                      p21_view_clippership_return_10004 ON p21_view_clippership_return_10004.pick_ticket_no = oe_pick_ticket.pick_ticket_no AND 

                      p21_view_clippership_return_10004.delete_flag = 'N' LEFT OUTER JOIN

                      freight_code ON freight_code.freight_code_uid = oe_pick_ticket.freight_code_uid LEFT OUTER JOIN

                      shipment AS ptshipment ON ptshipment.row_status_flag = 2175 AND ptshipment.transaction_type_cd = 1000 AND 

                      ptshipment.transaction_no = oe_pick_ticket.pick_ticket_no LEFT OUTER JOIN

                      address ON = COALESCE (ptshipment.carrier_id, oe_pick_ticket.carrier_id) LEFT OUTER JOIN

                      code_p21 AS carriertype ON carriertype.code_no = address.carrier_type_cd

WHERE     (NOT (oe_pick_ticket.delete_flag = 'Y')) AND (oe_hdr.order_no = '1086114') OR

                      (oe_hdr.order_no = '1086114') AND (NOT (oe_pick_ticket.pick_and_hold = 'Y'))

Open in new window

Question by:Jiggens
    LVL 16

    Expert Comment

    What exactly you need updated? The query seems correct it will give check for all the 3 values and return the 1st not null value but if all are nuull then it will return null. You could use this to return 'N/A' when all of them are null
    COALESCE (p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no, 'N/A') AS c_tracking_no

    Open in new window

    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you cannot "update" a query like this directly.
    you can only update table rows directly, or views if they have a INSTEAD OF trigger defined.

    to update, you have to run a UPDATE statement, and not a select.
    can you please clarify what you mean, hence, by "update"
    LVL 1

    Author Comment

    i want to update c_tracking_no i want to put in there the tracking number that is given to me from UPS Ground shipping
    LVL 1

    Author Comment

    I am trying to update the c_tracking_no to have a tracking number so its prints on my order.  Select query i didnt write when i go to the c_tracking_no the only way i see it is when COALESCE is being used.  I dont know where the actual data lives for c_tracking_no

    COALESCE(p21_view_clippership_return_10004.tracking_no, ptshipment.shipment_id, oe_pick_ticket.tracking_no) c_tracking_no,  this is auto generated from my application.

    I need to update order 1086114 with 1Z10A2R10394238914.

    Thats my goal.

    LVL 142

    Accepted Solution

    the general syntax to update 1 table from another:
    UPDATE d
       SET c_tracking_no = gs.c_tracking_no
      FROM p21_view_clippership_return_10004 d
      JOIN [UPS ground shipping]  gs
        ON <join condition>

    Open in new window

    LVL 1

    Author Comment

    angellll  i understand how i update a view but when i do that the data is not there. I tried just updating the table clippership_return_10004

    and it says 0 row(s) affected

    Any ideas where this data could actually be stored in my Database.  Is there a way i can search for this data?
    LVL 1

    Author Comment

    I guess i am missing some functionality. but when i go to either table and try selecting trackin_no on the table there is no data??
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    I am not sure what/how you did that... but ( 0 rows affected ) means  exactly that: no rows have been updated.
    means that either
    * the "join" condition made that 0 rows are matching
    * there are no rows at all in either tables

    as you might remember, I do know nothing about your data, and have only minimal information about the tables design.

    please clarify in simpler terms, you will eventually "see" the issue yourself.
    otherwise, explaining more details will help us see what you are doing "wrong".

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Suggested Solutions

    PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now