0

I have 2 tables RDR1 and ORDR. I currently use the below CASE statement to update the column UomCode in the RDR1 table:

Updated code below with the answer Update RDR1 Set UomCode = (CASE 

        WHEN LineStatus = 'C' and DelivrdQty = 0 THEN ''    
        WHEN LineStatus = 'C' and EncryptIV = NULL THEN 'DESPATCHED' 
        WHEN Quantity = DelivrdQty THEN 'DELIVERED'
        WHEN DelivrdQty = '0' THEN 'AWAITING STOCK'
        WHEN DelivrdQty < Quantity THEN 'PART SHIPPED'



    Else 'ERROR' End)

    from ORDR 
    inner join RDR1 
    On ORDR.DocEntry = RDR1.DocEntry

This works fine but I want to add a new line but that line requires a join but from reading examples online I cannot figure out where to put the join. The 2 tables join on a column called DocEntry

The statement will be 

WHEN RDR1.LineStatus = 'C' AND ORDR.U_ActualDel = NULL

Can anyone point me to an example or let me know where to put the join please?

John Spencer
  • 380
  • 5
  • 14
  • 1
    Use a `FROM` clause. `UPDATE R SET UomCode = ... FROM RDR1 R JOIN ORDR O ON...` – Thom A Aug 15 '19 at 14:01
  • That is what I saw in the examples but I need to do it all in 1 statement, If I run it twice it overwrites the other options with NULL's, Im not sure how to nest both updates in 1 case statement. – John Spencer Aug 15 '19 at 14:05
  • 2
    Need to do "what" in one statement? That *is* one statement. Are you asking how to `UPDATE` both `RDR1` and `ORDR` in 1 statement? If so, the answer is "you can't"; a DML statement can only effect one object. If this doesn't help you, then provide consumable sample data and your expected results, so that we can help you. – Thom A Aug 15 '19 at 14:08
  • 2
    The answer you seek begins here. https://stackoverflow.com/help/minimal-reproducible-example – Sean Lange Aug 15 '19 at 14:09
  • Sorry for the rubbish explanation. I am trying to add the join that you stated and update a single column by adding the join to my existing SQL I posted at the top. I will give a better example, apologies its harder to write out than explain. – John Spencer Aug 15 '19 at 14:31
  • Thanks @Larnu I have edited my answser to reflect the changes I needed, thank you for pointing me in the right direction. – John Spencer Aug 20 '19 at 20:53

0 Answers0