Ok.....

I've edited the test data to include another transaction for the same 'Facility_id' (In the live data there would be 100's of Facility_ids with multiple transactions)

The test data include the...

Well it did and it didnt? The only issue i discovered when i ran it against the full datset was the final record for each "Facility_ID" didnt get an update!!!...

Jeff,

Thanks for the information about UPDATE queries only updating a row once, I never realised this but now everything makes sense.

Looks like i'll be writting a loop and firing off...

Yes it does work, cant beleive I missed the '=' in your original reply..

Just out of curiosity why doesnt between work?

Thanks again, i'll try this on the whole dataset as...

In my head the column is updated with a 2 initially then its overwritten with a 3 when the next line of data is pulled down from the update query!

Try...

Thanks for your help, i hope all this works....

-- this table is the one that needs updating.....

CREATE TABLE [dbo].[test_days](

[facility_id] [varchar](20) COLLATE Latin1_General_CI_AS NULL,

[in_date] [datetime] NULL,

[mhsection] [varchar](50) COLLATE Latin1_General_CI_AS NULL

) ON...

Thanks for the reply,

Yep i've tried that and i get the same result, its almost like ignores the previous row as its just been updated?

I've used this type of query...

Thanks Stewartc,

The SSIS route worked fine, its just a pain I have to create a package for every load. I'm guessing there's no way to do this via the import/export...