SQL Update Query & Auto Run SQL Query

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 Development (2000)

SQL Update Query & Auto Run SQL Query

Author

Topic

marcfarmen
Starting Member

1 Post

Posted - 2006-02-10 : 17:02:51

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-11 : 18:47:45

Marc Welcome to SQLTeam

You can create a stored procedure and schedule this to run every hour.

Also, it seems that you are trying to set Organisation to 1st 2 chars of Address - 2 ?

UPDATE Client
SET    Organisation = convert(varchar(2), convert(int, left(Address, 2)))
WHERE  (Address LIKE '99%')

----------------------------------
'KH'
everything that has a beginning has an end

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-11 : 19:21:59

>>What is the most efficient way to accomplish this task
Are you talking about the hourly sceduling or looking for a better update query?

for scheduling look at using a sql job.
for more efficient code this could work:


use pubs
set nocount on
go

create table client (clientid int, organization varchar(3) null, address varchar(50))
go
insert client
select 1, null, '23YYYYYY' union all
select 2, null, '01NNNNN' union all
select 4, null, '99 lasdlkjadf' union all
select 3, null, 'NNNNNN' 
go

update a set
	Organization = convert(varchar,convert(int, b.org) + 2)
from	Client a
join	(
	select clientid
		,left(address, 2) org
	from	client
	where left(address, 2) like '[0-9][0-9]'
	and	organization is null
	) b
		on b.clientid = a.clientid


select * from client
go
drop table client

output:


clientid    organization address                                            
----------- ------------ -------------------------------------------------- 
1           25           23YYYYYY
2           3            01NNNNN
4           101          99 lasdlkjadf
3           NULL         NNNNNN

EDIT:
you should probably put something in to prevent the same row from being updated over and over again. I'll add one possible way to the query above
Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources