CODEWITHSUNDEEP
sqljoindb2ibm-midrange
Mahawkee
Mahawkee

Reputation: 35

Updating a table using JOIN

I'm trying to compose an SQL statement but keep getting errors...

Here is my situation:

I have a table with sales orders and a table with item transfers.

The sales orders have an order number and an extension, where the ID itself might not be unique, but the combination of order number and extension is. The extension can be null.

The item transfer table has a reference number and sometimes a sales order number plus extension, but not always.

The reason is that sometimes items are transferred for a sales order, sometimes for other reasons. Also, a sales order can happen without the item being transferred first. One order can only have one transfer though, and vice versa.

I added a field "transref" to the sales order table so an order can be connected to a transfer, if applicable. (This info can't be computed on-the-fly for performance reasons.) So my tables now look like this (they are actually bigger but this is the important info):

SALESORDERS
ORDERNO ORDEXT TRANSREF
1       (null) (null)
2       (null) (null)
2       a      (null)
3       (null) (null)

TRANSFERS
TRANSREF ORDERNO ORDEXT 
t1       1       (null)
t2       (null)  (null)
t3       2       a

Now I need to get the transref code into the sales orders table so that it looks like this: 

SALESORDERS
ORDERNO ORDEXT TRANSREF
1       (null) t1
2       (null) (null)
2       a      t3
3       (null) (null)

I tried all kinds of statements, e.g.

UPDATE SALESORDERS 
INNER JOIN TRANSFERS 
ON SALESORDERS.ORDERNO = TRANSFERS.ORDERNO and (SALESORDERS.ORDEXT = TRANSFERS.ORDEXT or (SALESORDERS.ORDEXT is null and TRANSFERS.ORDEXT is null))
SET SALESORDERS.TRANSREF = TRANSFERS.TRANSREF 
WHERE TRANSFERS.ORDERNO IS NOT NULL

but nothing worked so far. Can someone help me untie the knot I got in my brain after trying a dozen tutorials and answers for this?

Upvotes: 2

Views: 6540

Answers (4)

Dan
Dan

Reputation: 1

MERGE works fine, either embedded in SQLRPGLE or with interactive STRSQL.

Upvotes: 0

Buck Calabro
Buck Calabro

Reputation: 7633

Since you've called it an AS/400 several times, I'm going to assume you're on an old version of the operating system. You probably want a correlated subselect. Something like:

update salesorders o
set transref = (select t.transref 
  from transfers t
  where o.orderno = t.orderno
    and (o.ordext = t.ordext or (t.ordext=null and o.ordext=null))
    and t.transref <> null)
where ordext = null;

Upvotes: 3

dan1111
dan1111

Reputation: 6566

You are having this problem because DB2 doesn't allow you to use a JOIN with a simple UPDATE statement (one of its many seemingly arbitrary restrictions).

The best way to do this in DB2 is to use MERGE. Something like this should work:

merge into salesorders
    using transfers
    on transfers.orderno = salesorders.orderno and (
           transfers.ordtext = salesorders.ordtext or (
               transfers.ordtext is null and 
               salesorders.ordtext is null
           )
       )
    when matched then update set
        salesorders.transref = transfers.transref;

Upvotes: 0

podiluska
podiluska

Reputation: 51514

The syntax for this depends entirely on your variety of SQL

For instance, this may work (SQL Server)

UPDATE SALESORDERS  
SET SALESORDERS.TRANSREF = TRANSFERS.TRANSREF  
FROM
SALESORDERS  
INNER JOIN TRANSFERS  
ON SALESORDERS.ORDERNO = TRANSFERS.ORDERNO and (SALESORDERS.ORDEXT = TRANSFERS.ORDEXT or (SALESORDERS.ORDEXT is null and TRANSFERS.ORDEXT is null)) 
WHERE TRANSFERS.ORDERNO IS NOT NULL

Upvotes: 2

Related Questions