Reverse Update of 2 Columns - Tricky Tricky

Question

Reverse Update of 2 Columns - Tricky Tricky

SSC Enthusiast

Points: 137

June 5, 2004 at 10:15 am

Got a tricky one... I need to update CustomerID and PageNumber with values in rows that precede it. The first table is what I'm faced with... the second is what I need it to look like. The biggest obsitcle I have is with updating only up to the point where there is another CustomerID and it's updating in reverse order. Any help would be much appreciated!!!!

Current Table

ImportID	ID	EmployeeID	Year	CustomerID	PageNumber
1	21975	X1105	1997
2	22067	X1001	1997
3	22097	X1025	1997
4	22108	X1274	1997
5	21905	X1001	1997
6	21974	X1105	1997
7	22140	X1105	1997
8	22146	X1003	1997
9	22174	X1022	1997
11	21966	X1105	1997
12	22065	X1003	1997
13	22040	X1022	1997
14	22091	X1001	1997
15	22012	X1225	1997
16	22078	X1105	1997
17	22081	X1012	1997
18	22149	X1002	1997
19	Page 1
20	GTEMO28274
21	22074	X1002	1997
22	22079	X1120	1997
23	22114	X1003	1997
24	22199	X1022	1997
25	22220	X1120	1997
27	22240	X1001	1997
28	21965	X1002	1997
29	22077	X1105	1997
31	22093	X1533	1997
32	22109	X1488	1997
34	22148	X1358	1997
35	21871	X1358	1997
36	22161	X1358	1997
37	21907	X1005	1997
38	22150	X1485	1997
39	22193	X1022	1997
40	22216	X1003	1997
41	22228	X1022	1997
42	Page 2
43	GTEMO28275

What the table should look like... I will be deleting the 2 rows with CustomerID and PageNumber after the update.

ImportID	ID	EmployeeID	Year	CustomerID	PageNumber
1	21975	X1105	1997	GTEMO28274	Page 1
2	22067	X1001	1997	GTEMO28274	Page 1
3	22097	X1025	1997	GTEMO28274	Page 1
4	22108	X1274	1997	GTEMO28274	Page 1
5	21905	X1001	1997	GTEMO28274	Page 1
6	21974	X1105	1997	GTEMO28274	Page 1
7	22140	X1105	1997	GTEMO28274	Page 1
8	22146	X1003	1997	GTEMO28274	Page 1
9	22174	X1022	1997	GTEMO28274	Page 1
11	21966	X1105	1997	GTEMO28274	Page 1
12	22065	X1003	1997	GTEMO28274	Page 1
13	22040	X1022	1997	GTEMO28274	Page 1
14	22091	X1001	1997	GTEMO28274	Page 1
15	22012	X1225	1997	GTEMO28274	Page 1
16	22078	X1105	1997	GTEMO28274	Page 1
17	22081	X1012	1997	GTEMO28274	Page 1
18	22149	X1002	1997	GTEMO28274	Page 1
19	Page 1
20	GTEMO28274
21	22074	X1002	1997	GTEMO28275	Page 2
22	22079	X1120	1997	GTEMO28275	Page 2
23	22114	X1003	1997	GTEMO28275	Page 2
24	22199	X1022	1997	GTEMO28275	Page 2
25	22220	X1120	1997	GTEMO28275	Page 2
27	22240	X1001	1997	GTEMO28275	Page 2
28	21965	X1002	1997	GTEMO28275	Page 2
29	22077	X1105	1997	GTEMO28275	Page 2
31	22093	X1533	1997	GTEMO28275	Page 2
32	22109	X1488	1997	GTEMO28275	Page 2
34	22148	X1358	1997	GTEMO28275	Page 2
35	21871	X1358	1997	GTEMO28275	Page 2
36	22161	X1358	1997	GTEMO28275	Page 2
37	21907	X1005	1997	GTEMO28275	Page 2
38	22150	X1485	1997	GTEMO28275	Page 2
39	22193	X1022	1997	GTEMO28275	Page 2
40	22216	X1003	1997	GTEMO28275	Page 2
41	22228	X1022	1997	GTEMO28275	Page 2
42	Page 2
43	GTEMO28275

Thanks, Rich!!!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Razvan Socol SSCarpal Tunnel Points: 4811 More actions · Answer 1

This should work (but it's untested):

UPDATE YourTable SET PageNumber=(
 SELECT ID FROM YourTable B WHERE B.ImportID=(
  SELECT MIN(ImportID) FROM YourTable C
  WHERE C.ImportID>A.ImportID 
  AND ID LIKE 'Page %'
)) FROM YourTable A

UPDATE YourTable SET CustomerID=(
 SELECT ID FROM YourTable B WHERE B.ImportID=(
  SELECT MIN(ImportID)+1 FROM YourTable C
  WHERE C.ImportID>A.ImportID 
  AND ID LIKE 'Page %'
)) FROM YourTable A

Razvan

rtaubin SSC Enthusiast Points: 137 More actions · Answer 2

Perfecto! Very Nice! I was heading in the direction of a cursor. Thank you very much for you time on this!!!

Rich