We help IT Professionals succeed at work.

Updating a COALESCE

Medium Priority
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, address.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 address.id = 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

Watch Question

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

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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"


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


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.

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
the general syntax to update 1 table from another:
   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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


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?


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??
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

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".
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.