UPDATE Query - 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

General SQL Server Forums

New to SQL Server Programming

UPDATE Query

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2010-11-26 : 04:59:44

Hello

I am wanting to save an update query on my SQL database. Can someoen advise how best to do this please?

Eventually I want this to be run daily from an automated task but at the moment i'm having a problem in where to save it.

UPDATE dbo.hoursediting
SET Duration = 180
WHERE (Duration = 195) AND (CalculatedDuration = 195)AND Code NOT LIKE 'V%'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 05:06:30

How do you want to run it?
If it's via a scheduled job then you can embed the code in a step in the job.
Better to create a stored procedure in the database affected then run that

create procedure s_Update_hoursediting
as
UPDATE dbo.hoursediting
SET Duration = 180
WHERE (Duration = 195) AND (CalculatedDuration = 195)AND Code NOT LIKE 'V%'
go

then you run it (manually or in the job step) via
exec s_Update_hoursediting

You might also want to log that it has been run and how many rows it affected.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 01:43:50

in where condition you doesnt seem to have any condition based on time.this might end up updating same records again and again. you might have to add a condition based on date/time to make sure you pick up only incremental records each time

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

chipembele
Posting Yak Master

106 Posts

Posted - 2010-11-28 : 15:45:16

quote:
Originally posted by nigelrivett

How do you want to run it?
If it's via a scheduled job then you can embed the code in a step in the job.
Better to create a stored procedure in the database affected then run that

create procedure s_Update_hoursediting
as
UPDATE dbo.hoursediting
SET Duration = 180
WHERE (Duration = 195) AND (CalculatedDuration = 195)AND Code NOT LIKE 'V%'
go

then you run it (manually or in the job step) via
exec s_Update_hoursediting

You might also want to log that it has been run and how many rows it affected.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Thanks for your reply.

Eventually I'd like to have it run as a scheduled task overnight. At the moment i'm just trying to get it set up and saved on SQL server.

Is it possible to have multiple commends like this?

UPDATE dbo.hoursediting
SET Duration = 180
WHERE (Duration = 195) AND (CalculatedDuration = 195)AND Code NOT LIKE 'V%'

UPDATE dbo.hoursediting
SET Duration = 195
WHERE (Duration = 210) AND (CalculatedDuration = 210)AND Code NOT LIKE 'V%'

UPDATE dbo.hoursediting
SET Duration = 210
WHERE (Duration = 225) AND (CalculatedDuration = 225)AND Code NOT LIKE 'V%'

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-28 : 16:09:21

Yes - in the step or (better) in an sp.
You could also make this a single statament with a case statement.
UPDATE dbo.hoursediting
SET Duration =
case Duration
when 195 then 180
when 210 then 195
when 225 then 210
end
WHERE
(((Duration = 195) AND (CalculatedDuration = 195))
or ((Duration = 210) AND (CalculatedDuration = 210))
or ((Duration = 225) AND (CalculatedDuration = 225))
)
AND Code NOT LIKE 'V%'

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-28 : 16:13:33

could also be
UPDATE dbo.hoursediting
SET Duration = Duration - 15
WHERE Code NOT LIKE 'V%'
AND Duration = CalculatedDuration
AND Duration in (195,210,225)

But that might not represent the business requirements as well so wouldn't be as maintainable.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources