Update row based on value of multiple other rows in Oracle SQL

Question

I want to find the rows which are similar to each other, and update a field if a row has any similar row. My table looks like this:

OrderID  |  Price  | Minimum Number | Maximum Number | Volume | Similar 
1         45        2                 10                250        0  
2         46        2                 10                250        0   
3         60        2                 10                250        0

"Similar" in this context means that the rows that have same Maximum Number, Minimum Number, and Volume. Prices can be different, but the difference can be at most 2.

In this example, orders with OrderID of 1 and 2 are similar, but 3 has no similar row (since even if it has same Minimum Number, Maximum Number, and Volume, but its price is not within 2 units from orders 1 and 2).

Then, I want to update the filed "Similar" for orders 1 and 2 from the default value (0) to 1. So, the output for the example above would be:

OrderID  |  Price  | Minimum Number | Maximum Number | Volume | Similar 
1         45        2                 10                250        1
2         46        2                 10                250        1
3         60        2                 10                250        0

Gordon Linoff · Accepted Answer · 2014-05-03 12:33:47Z

Here is one method that is ANSI standard SQL that will work in most databases, including Oracle. It implements the logic that you set out using a correlated subquery:

update table t
    set similar = 1
    where exists (select 1
                  from table t2
                  where t2.minimum = t.minimum and
                        t2.maximum = t.maximum and
                        t2.volume = t.volume and
                        abs(t2.price - t.price) <= 2 and
                        t2.OrderId <> t.OrderId
                 );

EDIT:

It occurs to me that the "similar" field might be the minimum OrderId of the similar fields. You can extend the above idea to:

update table t
    set similar = (select min(orderId)
                   from table t2
                   where t2.minimum = t.minimum and
                         t2.maximum = t.maximum and
                         t2.volume = t.volume and
                         abs(t2.price - t.price) <= 2 and
                         t2.OrderId <> t.OrderId
                  )
    where exists (select 1
                  from table t2
                  where t2.minimum = t.minimum and
                        t2.maximum = t.maximum and
                        t2.volume = t.volume and
                        abs(t2.price - t.price) <= 2 and
                        t2.OrderId <> t.OrderId
                 );

Although if this were the case, the default value should be NULL and not 0.

Collectives™ on Stack Overflow

Update row based on value of multiple other rows in Oracle SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related