[Last Call] Learn how to a build a cloud-first strategyRegister Now


Updating a COALESCE

Posted on 2009-02-09
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

Question by:Jiggens
  • 4
  • 3
LVL 16

Expert Comment

ID: 23591023
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23591034
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"

Author Comment

ID: 23591040
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.


Author Comment

ID: 23591109
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 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 23591111
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


Author Comment

ID: 23591367
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?

Author Comment

ID: 23591527
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 23593745
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

829 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