Joining against same table

Question

Post reply

Joining against same table

Dylan Morley

SSC Enthusiast

Points: 164

July 19, 2006 at 4:12 am

#118787

Hi,

I need to update data in a table with the maximum of the eh_batch_reference field for all orders that have not been sent.

Consider the following data...

eh_id_pk	eh_order_id_fk	eh_edi_reference	eh_batch_reference	eh_order_sent	eh_completed_date
2	51	H015521552	S0611101	1	01/07/2006
1	51	H015521553	S0611102	1	01/07/2006
9	51			0
3	52	H064210051	S0611101	1	01/07/2006
4	52	H064210052	S0611102	1	01/07/2006
5	52			0
6	52	H064210053	S0611103	1	01/07/2006
7	52	H064210054	S0611104	1	01/07/2006
8	52	H064210055	S0611105	1	01/07/2006

In this above data, there are two orders that need to be updated with the last reference for the same orders.

order_id_fk 51 needs to have its batch reference set to S0611102 and order_id_fk 52 needs S0611105

Whats the best SQL to join against the same table and get the maximum of the field as detailed above??

Thanks for any help!

Dylan

Viewing 5 posts - 1 through 4 (of 4 total)

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Dylan

Try this.

John

--Table
CREATE TABLE #eh_batch_reference 
 (eh_id_pk INT, eh_order_id_fk INT, eh_edi_reference VARCHAR(20),
  eh_batch_reference VARCHAR(20), eh_order_sent tinyint, eh_completed_date datetime)

--Data
INSERT INTO #eh_batch_reference VALUES (2, 51, 'H015521552',  'S0611101',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (1, 51, 'H015521553',  'S0611102',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (9, 51,  NULL,          NULL,       0,  NULL       &nbsp
INSERT INTO #eh_batch_reference VALUES (3, 52, 'H064210051',  'S0611101',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (4, 52, 'H064210052',  'S0611102',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (5, 52,  NULL,          NULL,       0,  NULL       &nbsp
INSERT INTO #eh_batch_reference VALUES (6, 52, 'H064210053',  'S0611103',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (7, 52, 'H064210054',  'S0611104',  1, '01/07/2006 ')
INSERT INTO #eh_batch_reference VALUES (8, 52, 'H064210055',  'S0611105',  1, '01/07/2006 ')

--Update
UPDATE #eh_batch_reference SET eh_batch_reference = e2.ebr 
FROM #eh_batch_reference e1 JOIN (
 SELECT eh_order_id_fk, MAX(eh_batch_reference) AS ebr
 FROM #eh_batch_reference GROUP BY eh_order_id_fk
 ) e2
ON e1.eh_order_id_fk = e2.eh_order_id_fk

--Results
SELECT * FROM #eh_batch_reference

Dylan Morley SSC Enthusiast Points: 164 More actions · Answer 2

Thanks John - thats not quite what I need to do though...

I need to preserve the batch references of all the previous orders....it's just the 2 records that have value eh_order_sent = 0 that I need to update.

These 2 records need to updated with the maximum value of eh_batch_reference for the same eh_order_id_fk.

Is there no way to do it in a single SQL statement without creating a temporary table?

Cheers for the help!

Sergiy SSC Guru Points: 110208 More actions · Answer 3

So, add WHERE eh_order_sent = 0 at the end of UPDATE statement.

_____________
Code for TallyGenerator

Dylan Morley SSC Enthusiast Points: 164 More actions · Answer 4

Yep, so in one statement it would be...

UPDATE

CODBA.edi_history

SET

eh_batch_reference = e2.ebr

FROM

CODBA.edi_history e1

JOIN

( SELECT eh_order_id_fk, MAX(eh_batch_reference) AS ebr FROM CODBA.edi_history GROUP BY eh_order_id_fk ) e2

ON

e1.eh_order_id_fk = e2.eh_order_id_fk

WHERE

e1.eh_order_sent = 0

...eliminating the need for the temporary table. cheers!