Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update Cursor Advice Expand / Collapse
Author
Message
Posted Tuesday, December 10, 2013 4:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 10:16 AM
Points: 88, Visits: 549
Hi,

I have two tables Main & OrderReturn


CREATE TABLE [dbo].[Main](
[orderID] [varchar](15) NULL,
[orderLine] [int] NULL,
[mpn] [varchar](25) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[OrderReturn](
[orderID] [varchar](15) NULL,
[orderLine] [int] NULL,
[mpn] [varchar](25) NULL
) ON [PRIMARY]

GO


insert into Main values ('EE170828',1,'ABC654081-B21CBA')
insert into Main values ('EE170828',2,'ABC654081-B21#B19CBA')
insert into Main values ('EE170828',3,'ABC654764-L21CBA')
insert into Main values ('EE170828',4,'ABC654764-B21CBA')
insert into Main values ('EE170828',5,'ABC654764-B21#0D1CBA')
insert into Main values ('EE170828',6,'ABC647893-B21CBA')
insert into Main values ('EE170828',7,'ABC647893-B21#0D1CBA')
insert into Main values ('EE170828',8,'ABC652564-B21CBA')
insert into Main values ('EE170828',9,'ABC652564-B21#0D1CBA')
insert into Main values ('EE170828',10,'ABC684208-B21CBA')
insert into Main values ('EE170828',11,'ABC661069-B21CBA')
insert into Main values ('EE170828',12,'ABC661069-B21#0D1CBA')
insert into Main values ('EE170828',13,'ABC734807-B21CBA')
insert into Main values ('EE170828',14,'ABC734807-B21#0D1CBA')
insert into Main values ('EE170828',15,'ABCAP770BCBA')
insert into Main values ('EE170828',16,'ABCAP770B#0D1CBA')
insert into Main values ('EE170828',17,'ABC656362-B21CBA')
insert into Main values ('EE170828',18,'ABC656362-B21#0D1CBA')
insert into Main values ('EE170828',19,'ABCC6N36ABECBA')
insert into Main values ('EE170828',20,'ABC654081-B21CBA')
insert into Main values ('EE170828',21,'ABC654081-B21#B19CBA')
insert into Main values ('EE170828',22,'ABC654764-L21CBA')
insert into Main values ('EE170828',23,'ABC654764-B21CBA')
insert into Main values ('EE170828',24,'ABC654764-B21#0D1CBA')
insert into Main values ('EE170828',25,'ABC647893-B21CBA')
insert into Main values ('EE170828',26,'ABC647893-B21#0D1CBA')
insert into Main values ('EE170828',27,'ABC652564-B21CBA')
insert into Main values ('EE170828',28,'ABC652564-B21#0D1CBA')
insert into Main values ('EE170828',29,'ABC684208-B21CBA')
insert into Main values ('EE170828',30,'ABC661069-B21CBA')
insert into Main values ('EE170828',31,'ABC661069-B21#0D1CBA')
insert into Main values ('EE170828',32,'ABC734807-B21CBA')
insert into Main values ('EE170828',33,'ABC734807-B21#0D1CBA')
insert into Main values ('EE170828',34,'ABCAP770BCBA')
insert into Main values ('EE170828',35,'ABCAP770B#0D1CBA')
insert into Main values ('EE170828',36,'ABC656362-B21CBA')
insert into Main values ('EE170828',37,'ABC656362-B21#0D1CBA')
insert into Main values ('EE170828',38,'ABCC6N36ABECBA')
insert into Main values ('EE170828',39,'ABC654081-B21CBA')
insert into Main values ('EE170828',40,'ABC654081-B21#B19CBA')
insert into Main values ('EE170828',41,'ABC654780-L21CBA')
insert into Main values ('EE170828',42,'ABC647897-B21CBA')
insert into Main values ('EE170828',43,'ABC647897-B21#0D1CBA')
insert into Main values ('EE170828',44,'ABC652564-B21CBA')
insert into Main values ('EE170828',45,'ABC652564-B21#0D1CBA')
insert into Main values ('EE170828',46,'ABC684208-B21CBA')
insert into Main values ('EE170828',47,'ABC661069-B21CBA')
insert into Main values ('EE170828',48,'ABC661069-B21#0D1CBA')
insert into Main values ('EE170828',49,'ABC734807-B21CBA')
insert into Main values ('EE170828',50,'ABC734807-B21#0D1CBA')
insert into Main values ('EE170828',51,'ABC656362-B21CBA')
insert into Main values ('EE170828',52,'ABC656362-B21#0D1CBA')
insert into Main values ('EE170828',53,'ABCC6N36ABECBA')
insert into Main values ('EE170828',54,'ABCU4497ECBA')


insert into OrderReturn values ('EE170828',0,'ABC734807-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC647893-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21#B19CBA')
insert into OrderReturn values ('EE170828',0,'ABCU4497ECBA')
insert into OrderReturn values ('EE170828',0,'ABC734807-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC734807-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-L21CBA')
insert into OrderReturn values ('EE170828',0,'ABC647893-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABCAP770B#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABCC6N36ABECBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC647897-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21#B19CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABCC6N36ABECBA')
insert into OrderReturn values ('EE170828',0,'ABC647897-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC647893-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABCC6N36ABECBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABCAP770BCBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC647893-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC661069-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21#B19CBA')
insert into OrderReturn values ('EE170828',0,'ABC734807-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654780-L21CBA')
insert into OrderReturn values ('EE170828',0,'ABC652564-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC734807-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC734807-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC684208-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC656362-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-L21CBA')
insert into OrderReturn values ('EE170828',0,'ABC684208-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABC654764-B21#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABCAP770BCBA')
insert into OrderReturn values ('EE170828',0,'ABC684208-B21CBA')
insert into OrderReturn values ('EE170828',0,'ABCAP770B#0D1CBA')
insert into OrderReturn values ('EE170828',0,'ABC654081-B21CBA')


I want to update the field "orderLine" on the table "OrderReturn" from the values that are held in the table "Main". The join to each table is OrderID and mpn, but the table Main can have the same mpn appearing more than once.

I think cursor is required, so once it updates the orderLine for one of he mpn's it will then need to find the next available OrderLine to use for the same mpn.

Any ideas?

Thanks
Post #1521708
Posted Tuesday, December 10, 2013 11:08 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
A CURSOR is probably not required.

I'm not sure what exactly you want to do when there are duplicates, but try this and see if it gives you what you're looking for:

UPDATE OrderReturn
SET orderLine=
(
SELECT TOP 1 orderLine
FROM Main
WHERE OrderReturn.OrderID = Main.OrderID AND OrderReturn.mpn = Main.mpn
);

SELECT *
FROM OrderReturn
ORDER BY orderLine;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1521745
Posted Wednesday, December 11, 2013 2:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:14 AM
Points: 6,746, Visits: 12,838
There's an alternate interpretation of the question: the first match between the two tables (on OrderID, mpn) takes the first line number, the second match takes the second number and so on. All this requires is surrogate row numbering of OrderID, mpn partitions in both tables:

WITH
NumberedMain AS (
SELECT
OrderID, OrderLine, mpn,
rn = ROW_NUMBER() OVER(PARTITION BY OrderID, mpn ORDER BY OrderLine)
FROM Main
),
NumberedOrderReturn AS (
SELECT
OrderID, OrderLine, mpn,
rn = ROW_NUMBER() OVER(PARTITION BY OrderID, mpn ORDER BY OrderLine)
FROM OrderReturn
)
UPDATE r SET
OrderLine = m.OrderLine
FROM NumberedOrderReturn r
INNER JOIN NumberedMain m
ON m.OrderID = r.OrderID
AND m.mpn = r.mpn
AND m.rn = r.rn



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1521777
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse