I am attempting to write a simple update query that will automatically
be ran ever hour to update a records based on another columns value.
The below code works.

UPDATE Client 
SET Organisation = '3' 
WHERE (Address LIKE '01%') 

UPDATE Client 
SET Organisation = '4' 
WHERE (Address LIKE '02%') 

UPDATE Client 
SET Organisation = '5' 
WHERE (Address LIKE '03%') 

UPDATE Client 
SET Organisation = '6' 
WHERE (Address LIKE '04%') 

UPDATE Client 
SET Organisation = '7' 
WHERE (Address LIKE '05%') 

???Until I get to 99????. 

UPDATE Client 
SET Organisation = '97' 
WHERE (Address LIKE '99%') 

I would like to automate this process to look in the DB every hour and
change UPDATE the Orginisation Colum. I looked at Stored Procedures
but and lost. The Client table can be pretty large as many as 100,000
records. What is the most efficient way to accomplish this task. Any
help would be greatly appreciated.

Thanks 

Marc Farmen 
mfarmen@centennial-software.com

---

Request for Question Clarification by answerguru-ga on 10 Feb 2006 15:16 PST

Hi Marc,

I could write a stored procedure for you, but I feel that is more than
a $10 job. I would typically charge $75-100 for something like that
depending on the complexity. Is this something you would be interested
in having done?

If you are, please let me know about the following:

1. What database are you using (ie. MS SQL Server, mySQL, etc.)
2. Please provide me with some sample data from your Client table.
Since only the Address and Organization are involved, just data from
these two columns should suffice.
3. What are the data types of these two columns? (ie. varchar, int, etc.)

I look forward to hearing back from you.

Cheers,
answerguru-ga

---

Clarification of Question by marcfarmen-ga on 10 Feb 2006 15:56 PST

That sounds good. I may use this code in a Business application how
doses that work? Do you own the code?

Here are the answers to your questions.

1) MS SQL Server 2000 and MSDE
2) Data Type of Columns are Int

Sample Data of Each Colum


Address


Address
44454C4C-59001057-8054B3C0-4F4A304A
41D4CBA1-2ECF11D3-A110980E-EE23B8B6
935D889C-6D3D1790-41396860-5AE38DA7
1C2DF94E-392FD911-BBDA85DF-E2640011
834EAC3E-F784D811-BBD8CA75-D36D0040
3F69C0C0-4C6FD811-BBD87FA9-B527000E
44454C4C-5AA31037-8042B9C0-4F35304A
BAF91398-9196D811-BBDA7FF9-9F8D000E
9E2FD9BE-5ACDC01E-93DFACEB-7805E517
2AF02184-CB895F09-FF25C622-129984EC
91178BBB-C036874A-BC420AB7-284E1D83
ECF11C67-7FEC11D3-A03D0800-09E4C98A
44454C4C-4A00104B-8059B5C0-4F44304A
7ACB08DB-3833D911-AB7FD09E-2AB032E4
B8B11001-465411CB-8E74FE7C-40639ED7
6AEDD320-4D8AD811-BBDACD73-4936000B
33363831-33344742-4A353033-3033534E
F268578D-93E9D711-BBD89D51-1390000D
44454C4C-59001057-8054B4C0-4F4A304A
C2F3F211-984FD611-B4E886AF-8943F8E3
44454C4C-37001034-8030B8C0-4F4E304A
1DA953A4-994FD611-9D8088AF-8943F8E3
44454C4C-4DB11051-804AB2C0-4F37304A
B59D5BE6-3433D911-9F7BB49E-2AB032E4
44454C4C-56001056-804AB4C0-4F4E304A



Organization 

Organisation
13
17
10
7
17
17
20
17
1
2
8
2
1
1
12
6
19
9
9
1
9
5
2
1
12
12
4
19
4
19
10
19
1
17
3
19
1
4
20
16
17

Hi Marc,

In my opinion, creating an 'Update Trigger' AND/OR an 'Insert Trigger'
on the source table is a better option than creating a 'scheduled
job'. On every insert/update to the table, the appropriate sql
statement can be executed.

Here's some code (Insert trigger) to get you started:

----------------------

CREATE trigger tr_insert_source on Source_Table for insert as
declare @address varchar(100)
declare @sql varchar(100)
SELECT     @address = i.Address
 FROM    inserted i 

select @sql = 
	case left(@address,2)
		when '01' then "Update client set Organization=3 where Address like '01%'"
		when '02' then "Update client set Organization=5 where Address like '02%'"

	...
	...
	End

exec(@sql)


Note that if there is a pattern/relationship between Address and
Organization (It looks as though Organization = Int(Address) + 2), you
can write a TSql script that does this... Something that looks like :

declare @Id int
declare @Len varchar(10)
declare @zeroFill varchar(10)
declare @like varchar(10)
declare @organization varchar(10)
declare @sql varchar(100)

    select @Len = len(cast (@Id as char))
    select @zeroFill = replicate('0',2-cast(@Len as int)) + cast(@Id
as varchar(10))
    set @organization = cast(@zeroFill as int) + 2
    set @like = @zeroFill + '%'
    set @sql= 'Update Client set Organization=' + char(39) +
@organization + char(39) + ' where Address like ' + char(39) + 	@like
+ char(39)
    exec (@sql)


I hope this helps...

If you're willing to run extra programs on the server it would
probably take you about 30 mins to learn enough C# to write this VERY
simple program + you'll be able to add as many new queries as you want
for any timing you want.