Link to home
Start Free TrialLog in
Avatar of Vinum
VinumFlag for Denmark

asked on

SQL questioner


I have this table:
create table test (
ITEM VARCHAR(50),
OPR  INT,
PREVOPR INT,
NEXTOPR INT
)

I need to update the fields PREVOPR and NEXTOPR.
PREVOPR = OPR from previeous operation (first = 0)
NEXTOPR = OPR from next operation (last = 0)


Example data before update.
ITEM, OPR, PREVOPR, NEXTOPR
A       5       0       0
A       25      0       0
A       45      0       0
A       75      0       0
B       10      0       0
B       20      0       0
B       30      0       0
B       40      0       0
C       5       0       0
C       20      0       0
C       45      0       0


Example data after update:
ITEM, OPR, PREVOPR, NEXTOPR
A       5       0       25
A       25      5       45
A       45      25      75
A       75      45      0
B       10      0       20
B       20      10      30
B       30      20      40
B       40      30      0
C       5       0       20
C       20      5       45
C       45      20      0

Does any of you have an idea how to code this SQL ???

The original table will have about 150.000 of theese operations
Avatar of HainKurt
HainKurt
Flag of Canada image

try this:

with c as (
select *, row_number() over (order by item, opr) rn
)
update c
set c.prevopr=cp.opr, c.netxopr=cn.opr
from c
left join c cp  on c.rn=cp.rn+1
left join c cn  on c.rn=cp.rn-1;

Avatar of Kevin Cross
Minor tweak to HainKurt's solution given you sample output:

with c as (
   select ITEM, OPR, PREVOPR, NEXTOPR
        , row_number() over (partition by ITEM order by OPR) rn
   from test
)
update c
set c.prevopr=coalesce(cp.opr, 0)
    , c.netxopr=coalesce(cn.opr, 0)
from c
left join c cp  on c.rn=cp.rn+1
left join c cn  on c.rn=cp.rn-1;
;
some fixes ;) replace  "opr" on first line with your org table:

with c as (
select *, row_number() over (order by item, opr) rn from opr
)
update o
set o.prevopr=cp.opr, o.nextopr=cn.opr
from c o
left join c cp on o.rn=cp.rn+1 and o.item=cp.item
left join c cn on o.rn=cn.rn-1 and o.item=cn.item
WITH cte (ITEM, OPR, RowNum) AS
(
   SELECT
         ITEM,
         OPR,
         ROW_NUMBER() OVER (ORDER BY ITEM, OPR)
      FROM #Test
)
SELECT
      t1.ITEM,
      t1.OPR,
            t3.OPR PREVOPR,
      t2.OPR NEXTOPR
   FROM cte t1
      LEFT JOIN cte t2 ON t1.RowNum = t2.RowNum - 1
            LEFT JOIN cte t3 ON t1.RowNum = t3.RowNum + 1
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
mwvisa1, you are not taking ITEM into account... your cte query will give multiple records for a given rn... I guess it will not do what he wants...
I was trying to help tweak your query, so didn't catch that you didn't have ITEM in the JOIN.  PARTITION BY was added to account for ITEM. :)

with c as (
   select ITEM, OPR, PREVOPR, NEXTOPR
        , row_number() over (partition by ITEM order by OPR) rn
   from test
)
update c
set c.prevopr=coalesce(cp.opr, 0)
    , c.netxopr=coalesce(cn.opr, 0)
from c
left join c cp  on c.ITEM = cp.ITEM and c.rn=cp.rn+1
left join c cn  on c.ITEM = cn.ITEM and c.rn=cp.rn-1;
;

Your way now works because you are generating row numbers for everything, but then binding to ITEM.  You were not before and so would have resulting in ITEM not being taken into account as well.
And FYI, that is why I said minor tweak to your solution.  I was simply highlighting the errors, so glad you corrected them in your new post.
Avatar of Vinum

ASKER

To mwvisa1:
I tried your solution ID: 35737658
It only updates PREVOPR, not NEXTOPR


To HainKurt:
I tried your solution ID: 35737537
It seems like it does the job (only correctet ... "rn from opr" to "rn from test"

Personally, I like the CTE approach using the ROW_NUMBER() windowing function, which is why I support HainKurt's solution; however, here is an alternative for completeness of the thread -- will work on systems where CTE/ROW_NUMBER() are not available:

UPDATE tto
SET tto.PREVOPR = 
        COALESCE((
           SELECT MAX(OPR) 
           FROM test tfr 
           WHERE tfr.ITEM = tto.ITEM 
           AND tfr.OPR < tto.OPR
        ), 0)
   , tto.NEXTOPR = 
        COALESCE((
           SELECT MIN(OPR) 
           FROM test tfr 
           WHERE tfr.ITEM = tto.ITEM 
           AND tfr.OPR > tto.OPR
        ), 0)
FROM test tto
;

Open in new window

@Vinum, that wasn't meant to be my solution as I was pointing out to HainKurt.  I merely posted to advise you of corrections to his original query.  Therefore, those are type-o's from HainKurt's original post.  If you look cp.rn-1 should be cn.rn-1 and should be nextopr instead of netxopr.  At any rate, I wasn't looking for points there.  My reason for posting was if you ran the original query as posted, it would not have worked properly...

Best regards and happy coding,

Kevin
Avatar of Vinum

ASKER

Some info, maybe of interest

I tried the 2 solutions on a copy of the real DB's.
It consist of 3 tables:
Prod_order
Prod_opr (join Prod_order on fk_Prod_orderID)
Prod_opr_detail (join prod_opr on fk_Prod_oprID)

I have to use both Prod_opr and Prod_opr_detail in the update, as the uniqie key (fk_prod_orderid) is on Prod_opr (copied to Test_opr and Test_opr_detail)
Prod_opr and Prod_opr_detail is one-to-one match - about 328.000 records in each.

When updating with HainKurt's solution ("with...") it takes about 25 seconds.
When updating with mwvisa1's solution (traditional update), I don't know how long it takes - it never finished.
When running on a copy with only 1.000 records, it took about 10 seconds.
When running on a copy with 10.000 records, it had not finished after 5 minutes.


with c as (
select 
	rd.id as rdid, 
	rd.opr_no as rdopr_no,
	rd.prev_opr as rdprev_opr,
	rd.next_opr as rdnext_opr,
	ro.fk_prod_orderid as rofk_prod_orderid,
 	row_number() over (order by fk_prod_orderid, rd.id, rd.opr_no) rn 
from test_opr_detail rd 
	left join test_opr ro on rd.fk_prod_oprid = ro.id
)
update o
set o.rdprev_opr=isnull(cp.rdopr_no,0), o.rdnext_opr=isnull(cn.rdopr_no,0)
from c o
left join c cp on o.rn=cp.rn+1 and o.rofk_prod_orderid=cp.rofk_prod_orderid
left join c cn on o.rn=cn.rn-1 and o.rofk_prod_orderid=cn.rofk_prod_orderid

Open in new window

You probably have some indexing not in properly in place why the non-CTE approach took too long.  It is going to inefficient due to subqueries in both directions, which is why in a system where CTE is available I would use that.  As far as the CTE approach taking 25 seconds that might be an indication of poor indexing also.  Though 25 seconds isn't extremely horrible.
Anyway, thanks for the information.  It would have been good to see what the other query you tried that didn't finish though since it appears you rewrote this a bit.
Avatar of Vinum

ASKER

Hi mwvisa1

Below is the code. I am quite new in SQL programming, I am an old COBOL mainframe programmer, so it may not be the most professional programming in SQL :-)
UPDATE tto
SET tto.PREV_OPR = 
        COALESCE((
           SELECT MAX(tfr.OPR_NO) 
           FROM test_opr_detail tfr
				left join test_opr tfo on tfr.fk_prod_oprid = tfo.id 
           WHERE tfo.fk_prod_orderid = ttop.fk_prod_orderid 
           AND tfr.OPR_NO < tto.OPR_no
        ), 0)
   , tto.NEXT_OPR = 
        COALESCE((
           SELECT MIN(tfr.OPR_no) 
           FROM test_opr_detail tfr 
				left join test_opr tfo on tfr.fk_prod_oprid = tfo.id
           WHERE tfo.fk_prod_orderid = ttop.fk_prod_orderid
           AND tfr.OPR_NO > tto.OPR_NO
        ), 0)
FROM test_opr_detail tto
	left join test_opr ttop on tto.fk_prod_oprid = ttop.id

Open in new window

*smile* no worries, we are all having to constantly change and keep up with technology and as such are always learning.

Is it safe to say that fk_prod_oprid and fk_prod_orderid are dependent, i.e., if you have a unique match to fk_prod_orderid is it true that the fk_prod_oprid is same unique match always.  If so, you may get away with not joining in the test_opr table since you don't actually need any of the values in that table.  You really are just needing a column on which to group data ... would the fk_prod_oprid not work?
UPDATE tto
SET tto.PREV_OPR = 
        COALESCE((
           SELECT MAX(tfr.OPR_NO) 
           FROM test_opr_detail tfr
           WHERE tfr.fk_prod_oprid = tto.fk_prod_oprid
           AND tfr.OPR_NO < tto.OPR_no
        ), 0)
   , tto.NEXT_OPR = 
        COALESCE((
           SELECT MIN(tfr.OPR_no) 
           FROM test_opr_detail tfr 
           WHERE tfr.fk_prod_oprid = tto.fk_prod_oprid
           AND tfr.OPR_NO > tto.OPR_NO
        ), 0)
FROM test_opr_detail tto
;

Open in new window


If you do need the JOINs, I would consider an INNER JOIN versus LEFT OUTER JOIN there.

To simulate the situation, I created test_opr_detail table with 360448 rows and get a 10 second response using CTE and 24 seconds using subqueries.

Creating an index similar to this:
create nonclustered index idx_fk_prod_oprid on test_opr_detail(fk_prod_oprid) include(opr_no);

Open in new window


The CTE approach is still a bit faster at 9 seconds and the subqueries at 14 seconds.

In your environment you might find a greater improvement since you are seeing no response or minutes.  I would use the ESTIMATED EXECUTION PLAN avaialble in SQL Management Studio to see where the highest cost is within your query.   Again, I suspect it is indexing.

Moral, though, is the subqueries approach will be a bit slower.  The CTE approach I would expect faster, but depends on system resources.  I am running SQL Express edition on a laptop that is of decent power, so my getting 9-10 seconds versus your 24 isn't really unconceivable.

Anyway, thanks for sharing.  Intriguing to see the challenges folks face and see why something performs differently and determine the cause.  Helps me learn as well.  As I said, we are *all* still learning. *smile*