SQl Update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

SQl Update

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-02-16 : 07:56:07

Marc writes "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"

Kristen
Test

22859 Posts

Posted - 2006-02-16 : 08:05:08

Hi Marc, Welcome to SQL Team!

How about creating a TRIGGER on the Client table so that Organisation is set whenever Address changes (or is freshly created)?

Kristen

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-16 : 08:08:37

Do you miss a few numbers out? It's just that you start by setting Organisation higher than Address and finish by setting it lower..

I there is some logic, then you could do this in 1 update statement, otherwise you could just put it in an SP with:


CREATE PROCEDURE usp_UpdateOrganisation
AS
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%')

Then call it with:


EXEC usp_UpdateOrganisation

and set this up in a scheduled job..

mallier
Starting Member

24 Posts

Posted - 2006-02-16 : 10:08:44

Another option if u dont like trigger and too lazy to write lengthy code.

create proc usp_UpdateOrganisation
as
set nocount on
declare @sql varchar(500)
declare @i as int
declare @add varchar(10),@org varchar(10)
set @i=1
while(@i<=97)
begin
set @add=right('0'+cast(@i as varchar)+'%',3)
set @org=cast((@i+2) as varchar)
set @sql='UPDATE Client
SET Organisation ='''+@org+'''
WHERE Address LIKE '''+@add+''''
--print @sql
exec(@sql)
set @i=@i+1
end

cheers,
http://mallier.blogspot.com

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-16 : 10:11:34

quote:
Originally posted by mallier

Another option if u dont like trigger and too lazy to write lengthy code.

create proc usp_UpdateOrganisation
as
set nocount on
declare @sql varchar(500)
declare @i as int
declare @add varchar(10),@org varchar(10)
set @i=1
while(@i<=97)
begin
set @add=right('0'+cast(@i as varchar)+'%',3)
set @org=cast((@i+2) as varchar)
set @sql='UPDATE Client
SET Organisation ='''+@org+'''
WHERE Address LIKE '''+@add+''''
--print @sql
exec(@sql)
set @i=@i+1
end

cheers,
http://mallier.blogspot.com

But unless Marc's made a mistake in his post, that won't work for the last record..

mallier
Starting Member

24 Posts

Posted - 2006-02-16 : 10:57:08

quote:
Originally posted by RickD

quote:
Originally posted by mallier

Another option if u dont like trigger and too lazy to write lengthy code.

create proc usp_UpdateOrganisation
as
set nocount on
declare @sql varchar(500)
declare @i as int
declare @add varchar(10),@org varchar(10)
set @i=1
while(@i<=97)
begin
set @add=right('0'+cast(@i as varchar)+'%',3)
set @org=cast((@i+2) as varchar)
set @sql='UPDATE Client
SET Organisation ='''+@org+'''
WHERE Address LIKE '''+@add+''''
--print @sql
exec(@sql)
set @i=@i+1
end

cheers,
http://mallier.blogspot.com

But unless Marc's made a mistake in his post, that won't work for the last record..

I assumed it was his mistake.Otherwise he will make mistake with my code

cheers,
http://mallier.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources