--structure
declare @geographicalWeighting table (ItemValue1 varchar(9), ItemValue2 varchar(9), FUNDDATE datetime)
declare @FACTDATETABLE table (FACTDATE datetime)
--/

--data
insert @geographicalWeighting
          select 'OTHERS', 'CASH', '20091203'
union all select 'OTHERS', 'A', '20091203'
union all select 'OTHERS', 'B', '20091203'
union all select 'OTHERS', 'B', '20091202'

insert @FACTDATETABLE
          select '20091203'
union all select '20091202'
--/

--calculation
UPDATE @geographicalWeighting
SET ItemValue1 = 'CASH'
WHERE ItemValue1 = 'OTHERS'
AND ItemValue2 NOT LIKE 'CASH%'
AND FUNDDATE = (SELECT MAX(FACTDATE) FROM @FACTDATETABLE)
--/

--results
select * from @geographicalWeighting
--/

quote:

---

The following is the basic statement (as i remember it)

UPDATE geographicalWeighting
SET ItemValue1 = 'CASH'
WHERE ItemValue1 = 'OTHERS'
AND ItemValue2 NOT LIKE 'CASH%'
AND FUNDDATE = (SELECT MAX(FACTDATE) FROM FACTDATETABLE)

---

/*
Hi guys,

ok, here is the table structure :

GeographicWeighting :
FundDataDate datetime
FundId int 4
ItemName01 nvarchar 50
ItemName02 nvarchar 50
ItemValue01 float 8
ItemValue02 float 8
*/
IF OBJECT_ID('tempDb..#GeographicWeighting') IS NOT NULL DROP TABLE #GeographicWeighting
CREATE TABLE #GeographicWeighting (
	[fundDataDate] DATETIME
	, [FundId] INT -- No widths on INTS 4
	, [ItemName01] NVARCHAR(50)
	, [ItemName02] NVARCHAR(50)
	, [ItemValue01] FLOAT(8)
	, [ItemValue02] FLOAT(8)
	)

/*
Sample data :

30/11/2009	5	N America	Bonds - Govt	45	0	30/11/2009
30/11/2009	5	N America	Bonds - I/Linked	7	0	30/11/2009
30/11/2009	5	N America	F/R Notes	5	0	30/11/2009
30/11/2009	5	UK	Bonds - Corp	3	0	30/11/2009
30/11/2009	6	Europe	Bonds	16	0	30/11/2009
30/11/2009	6	Others	Equities	8	0	30/11/2009
30/11/2009	6	Others	Equities	8	0	30/11/2009
30/11/2009	6	Japan Equities	4	0	30/11/2009
30/11/2009	6	N America	Bonds	10	0	30/11/2009
30/11/2009	6	N America	Bonds - I/Linked	5	0	30/11/2009
30/11/2009	6	N America	Equities	16	0	30/11/2009
*/
INSERT #GeographicWeighting (
	[fundDataDate]
	, [FundId]
	, [ItemName01]
	, [ItemName02]
	, [ItemValue01]
	, [ItemValue02]
	)

          SELECT '20091130', 5, 'N America', 'Bonds - Govt', 45, 0
UNION ALL SELECT '20091130', 5, 'N America', 'Bonds - I/Linked', 7, 0
UNION ALL SELECT '20091130', 5, 'N America', 'F/R Notes', 5, 0
UNION ALL SELECT '20091130', 5, 'UK	Bonds', '- Corp', 3, 0
UNION ALL SELECT '20091130', 6, 'Europe', 'Bonds', 16, 0
UNION ALL SELECT '20091130', 6, 'Others', 'Equities', 8, 0
UNION ALL SELECT '20091130', 6, 'Others', 'Equities', 8, 0
UNION ALL SELECT '20091130', 6, 'Japan', 'Equities', 4, 0
UNION ALL SELECT '20091130', 6, 'N America', 'Bonds', 10, 0
UNION ALL SELECT '20091130', 6, 'N America', 'Bonds - I/Linked', 5, 0
UNION ALL SELECT '20091130', 6, 'N America', 'Equities', 16, 0
/*
The following is the code (that if applied to the above data would cause EVERY entry to be stamped with CASH...)

( the max date value being 30/11/2009)

UPDATE xxxweb02.fundfactsheetdev.dbo.GeographicalWeighting
SET itemname01 = 'Cash'
WHERE ( itemname01 = 'Others'
AND itemname02 NOT LIKE 'Cash%'
AND fundDataDate = (SELECT MAX(FundFactSheetDate) FROM xxxweb02.fundfactsheetdev.dbo.FundFactSheetDates) )
*/
UPDATE #GeographicWeighting
SET itemname01 = 'Cash'
WHERE (
	itemname01 = 'Others'
	AND itemname02 NOT LIKE 'Cash%'
	AND fundDataDate = '20091130'
	)

/*
The following code causes only 2 records from the above data example to be updated :

declare @processDate as datetime

set @processdate = (SELECT MAX(FundFactSheetDate) FROM xxxweb02.fundfactsheetdev.dbo.FundFactSheetDates)


UPDATE xxx.fundfactsheetdev.dbo.GeographicalWeighting
SET itemname01 = 'Cash'
FROM xxx.fundfactsheetdev.dbo.GeographicalWeighting 
WHERE itemname01 = 'Others'
AND funddatadate = @processDate


I MUST have been doing something stupid, but just cant see it...

Chris
*/

SELECT * FROM #GeographicWeighting

UPDATE #GeographicWeighting
SET itemname01 = 'Cash'
WHERE (
	itemname01 = 'Others'
	AND itemname02 NOT LIKE 'Cash%'
	AND fundDataDate = '20091130'
	)

itemname01 = 'Others'