Update issue on table join

Question

Post reply

Update issue on table join

DerbyNeal

Hall of Fame

Points: 3695
More actions
February 15, 2012 at 8:37 am

#253099

Hi,

We have two similar files, one header file and one line file. Under certain conditions*, the line file is populated with a value in the ‘Linked_File_Name’ column. There are some examples where the column should have a value but is currently set to NULL.

* conditions

lf.Mill = hf.Mill
AND lf.Customer = hf.Customer WHERE lf.Date_Produced >= hf.Date_Produced
AND lf.[YearMonth] >= hf.[YearMonth]

(i.e. lf = Line_File AND hf = Header_File)

Here are a few examples in each of the files:

Header_File

File_Name Code Customer Mill Date_Produced YearMonth

CUL_20110815_aug11_sw, 44, FF, CUL, 20110815, 201108

CUL_20110817_aug11_sw, 44, FF, CUL, 20110817, 201108

CUL_20110824_aug11_sw, 44, FF, CUL, 20110824, 201108

CUL_20110815_aug11_sw, 45, FF, CUL, 20110815, 201108

CUL_20110816_aug11_sw, 45, FF, CUL, 20110816, 201108

CUL_20110817_aug11_sw, 45, FF, CUL, 20110817, 201108

CUL_20110824_aug11_sw, 45, FF, CUL, 20110824, 201108

CUL_20110815_aug11_sw, 47, FF, CUL, 20110815, 201108

CUL_20110817_aug11_sw, 47, FF, CUL, 20110817, 201108

CUL_20110824_aug11_sw, 47, FF, CUL, 20110824, 201108

CUL_20110815_aug11_sw, 53, FF, CUL, 20110815, 201108

CUL_20110816_aug11_sw, 53, FF, CUL, 20110816, 201108

CUL_20110817_aug11_sw, 53, FF, CUL, 20110817, 201108

CUL_20110824_aug11_sw, 53, FF, CUL, 20110824, 201108

Line_File

File_Name Code Customer Mill Date_Produced YearMonth Linked_File_Name

CUL_20110817_aug11_sw, 44, FF, CUL, 20110817, 201108, CUL_20110817_aug11_sw

CUL_20110817_aug11_sw, 45, FF, CUL, 20110817, 201108, CUL_20110817_aug11_sw

CUL_20110812_aug11_sw, 47, FF, CUL, 20110812, 201108, NULL

CUL_20110818_aug11_sw, 47, FF, CUL, 20110818, 201108, NULL

CUL_20110818_aug11_sw, 53, FF, CUL, 20110818, 201108, NULL

In the above Line_File example, the first 2 records hold the correct value. However, whereas the 3rd record should be NULL due to the 'Date_Produced' condition, the 4th and 5th records should have values of 'CUL_20110817_aug11_sw'.

If I use the following query, it will obviously overwrite all of the Linked_File_Name fields with the same (top 1) value.

UPDATE dbo.Line_File
SET Linked_File_Name = (SELECT TOP 1 hf.[File_Name] FROM dbo.Line_File lf LEFT JOIN dbo.Header_File hf ON lf.Mill = hf.Mill AND lf.Customer = hf.Customer WHERE lf.Date_Produced >= hf.Date_Produced AND lf.[YearMonth] >= hf.[YearMonth] AND (lf.Linked_File_Name IS NULL) ORDER BY hf.[YearMonth] DESC,
hf.Date_Produced DESC)

Any ideas please.

Thanks in advance,

Neal

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

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

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

Not 100% sure what you're after, but this should do the trick:



UPDATE lf

SET Linked_File_Name = (

    SELECT TOP 1 hf.[File_Name]

    FROM dbo.Header_File hf

    WHERE   lf.Date_Produced    >= hf.Date_Produced

        AND lf.[YearMonth]      >= hf.[YearMonth]

        AND lf.Mill              = hf.Mill

        AND lf.Customer          = hf.Customer

        AND lf.Linked_File_Name IS NULL

    ORDER BY hf.[YearMonth] DESC,

        hf.Date_Produced DESC

)

FROM dbo.Line_File AS lf