August 8, 2005 at 2:06 pm
Here's my select :
select dest_bol_no, rev_no, max_rev_yorn from BL_TRUCK_BOL_DEST A
WHERE bol_no = '000001'
AND rev_no = (select max(rev_no) from BL_TRUCK_BOL_DEST B
where A.dest_bol_no = B.dest_bol_no)
Here's what I get:
dest_bol_no rev_no max_rev_yorn
000002BT.........32............
000003BT.........32............
000004BT.........8..............
000005BT.........10............
000006BT.........32............
Here's what I want:
dest_bol_no.....rev_no.....max_rev_yorn
000002BT.........32............Y
000003BT.........32............Y
000004BT.........8..............Y
000005BT.........10............Y
000006BT.........32.............Y
You'll notice that there are 5 rows but when I update it updates 9 rows. An example of the full table data is:
bol_no....dest_bol_no.....rev_no.... max_rev_yorn
000001..000002BT...........1....................
000001..000002BT...........2...................
000001..000002BT...........3...................
".................."..............."....................
000001..000002BT...........32..................
After the update, the table will havemax_rev_yorn = 'Y' for dest_bol_no = 000002BT for rev_no = 8,10. I only want it to update for rev_no = 32. It is picking up rev_no for other dest_bol_no's that I don't want.
August 8, 2005 at 2:29 pm
/* Your Select */
SELECT dest_bol_no,
rev_no,
max_rev_yorn
FROM
BL_TRUCK_BOL_DEST A
JOIN
(SELECT bol_no, MAX(rev_no) rev_no FROM BL_TRUCK_BOL_DEST B
WHERE A.dest_bol_no = B.dest_bol_no GROUP BY bol_no) B
ON
A.bol_no = B.bol_no AND
A.rev_no = B.rev_no
/* Your Update */
UPDATE A
SET
max_rev_yorn = 'Y'
FROM
BL_TRUCK_BOL_DEST A
JOIN
(SELECT bol_no, MAX(rev_no) rev_no FROM BL_TRUCK_BOL_DEST B
WHERE A.dest_bol_no = B.dest_bol_no GROUP BY bol_no) B
ON
A.bol_no = B.bol_no AND
A.rev_no = B.rev_no
Regards,
gova
August 8, 2005 at 2:29 pm
could you please post your update sql...?
**ASCII stupid question, get a stupid ANSI !!!**
August 8, 2005 at 2:33 pm
Here's my update:
UPDATE BL_TRUCK_BOL_DEST
Set max_rev_yorn = 'Y' WHERE rev_no in
(select rev_no from BL_TRUCK_BOL_DEST A
WHERE bol_no = '000001'
AND rev_no = (select max(rev_no) from BL_TRUCK_BOL_DEST B
where A.dest_bol_no = B.dest_bol_no))
AND
dest_bol_no in
(select dest_bol_no from BL_TRUCK_BOL_DEST A
WHERE bol_no = '000001'
AND rev_no = (select max(rev_no) from BL_TRUCK_BOL_DEST B
where A.dest_bol_no = B.dest_bol_no))
August 8, 2005 at 2:47 pm
try this
UPDATE A
SET
max_rev_yorn = 'Y'
FROM
BL_TRUCK_BOL_DEST A
JOIN
(SELECT bol_no, MAX(rev_no) rev_no FROM BL_TRUCK_BOL_DEST
WHERE bol_no = '000001' GROUP BY bol_no) B
ON
A.bol_no = B.bol_no AND
A.rev_no = B.rev_no
or
UPDATE A
SET
max_rev_yorn = 'Y'
FROM
BL_TRUCK_BOL_DEST A
JOIN
(SELECT MAX(rev_no) rev_no FROM BL_TRUCK_BOL_DEST
WHERE dest_bol_no = '000001') B
ON
A.rev_no = B.rev_no AND
A.bol_no = '000001'
Regards,
gova
August 8, 2005 at 3:00 pm
That doesn't work. Here's what it produces:
dest_bol_no...rev_no...max_rev_yorn
000002BT 32 .......Y
000003BT 32 .......Y
000004BT 8 ........
000005BT 10 ........
000006BT 32 ......Y
4BT & 5BT don't get updated.
August 8, 2005 at 3:23 pm
/* If this doesn't work please post the data in your table as below */
DECLARE @BL_TRUCK_BOL_DEST TABLE
(
bol_no VARCHAR(12),
dest_bol_no VARCHAR(12),
rev_no INT,
max_rev_yorn VARCHAR(1) NULL
)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 1, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 2, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 32, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000003BT', 2, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000003BT', 3, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000003BT', 32, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 12, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000004BT', 1, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000004BT', 2, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 2, NULL)
INSERT INTO @BL_TRUCK_BOL_DEST VALUES ('000001', '000002BT', 4, NULL)
UPDATE A
SET
max_rev_yorn = 'Y'
FROM
@BL_TRUCK_BOL_DEST A
JOIN
(SELECT bol_no, dest_bol_no, MAX(rev_no) rev_no FROM @BL_TRUCK_BOL_DEST
GROUP BY bol_no, dest_bol_no) B
ON
A.bol_no = B.bol_no AND
A.rev_no = B.rev_no AND
A.dest_bol_no = B.dest_bol_no
SELECT * FROM @BL_TRUCK_BOL_DEST ORDER BY 2, 3
Regards,
gova
August 8, 2005 at 3:33 pm
GOVINN -
That worked great. I had to add a where clause though. Thank you very much - it really helped me out.
UPDATE A
SET
max_rev_yorn = 'Y'
FROM
BL_TRUCK_BOL_DEST A
JOIN
(SELECT bol_no, dest_bol_no, MAX(rev_no) rev_no FROM BL_TRUCK_BOL_DEST
WHERE bol_no = '000001'
GROUP BY bol_no, dest_bol_no) B
ON
A.bol_no = B.bol_no AND
A.rev_no = B.rev_no AND
A.dest_bol_no = B.dest_bol_no
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply