Is there a way? - 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

Transact-SQL (2000)

Is there a way?

Author

Topic

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 09:12:45

Good morning

Is there a way to Update a single record based on a month?
If so would you be so kind as to post an example of the syntax.

Here is what I hope to be able to do. (This is the original syntax)

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE ([ID] = '95146')AND([Sales Person] = 'Cynthia Frederick')

The problem is if there are multiple entries for 'Cynthia Frederick'
the syntax above will update all lines in which the statement is TRUE.
(This is why I used the [ID] field so that it will update only 1 line)

Given that this is a value which need to be updated only once month, Is there a way to instruct SQL to update a single line with the value for the month? without having to use the [ID] Field?

As always, Thanks for any assistance you may be able to provide

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-09-28 : 09:18:03

[code]SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE [Sales Person] = 'Cynthia Frederick'
GO

SET ROWCOUNT 0
GO[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:18:32

SET ROWCOUNT 1

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE ([ID] = '95146')AND([Sales Person] = 'Cynthia Frederick')

SET ROWCOUNT 0

E 12°55'05.25"
N 56°04'39.16"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 09:18:51

which lines do you want to update ? what is your criteria ?

KH
[spoiler]Time is always against us[/spoiler]

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 09:29:11

Good morning

Thank you so much for the help, My criteria is as follows

I could use the provided suggested syntax of:

SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE [Sales Person] = 'Cynthia Frederick'
GO

SET ROWCOUNT 0
GO

It seems this will update a single row within the table (Which is exactly what I need), but each line also has
a Date associated to it. Given this is there a way to do the syntax listed above based on a month i.e.

SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE [Sales Person] = 'Cynthia Frederick'
GO

SET ROWCOUNT 0
GO

"FOR THE MONTH OF SEPTEMBER 2007" which will update one single row, only on a record with a date that falls within the month of September 2007?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 09:31:43

is there a date column in your table ?

can you post your table structure ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 09:33:11

SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE [Sales Person] = 'Cynthia Frederick'
and datecol >= '20070901' and datecol < '20071001'
GO

SET ROWCOUNT 0

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 09:40:07

Thank you guys so much for your help

Peso, can I string this syntax, for instance if I have 10 Sales People, can I just duplicate the syntax for each Sales Person?

Thanks again
Eric

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 09:47:13

For instance, Can I do this with the syntax provided

SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '69673.00'
WHERE [Sales Person] = 'Cindy Grider'
and datecol >= '20070901' and datecol < '20071001'
GO

SET ROWCOUNT 0

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

SET ROWCOUNT 1
GO

UPDATE dbo.[TransNet Corporation$Sales Commission]
SET [Forcast] = '53629.00'
WHERE [Sales Person] = 'Cynthia Frederick'
and datecol >= '20070901' and datecol < '20071001'
GO

SET ROWCOUNT 0

Eric

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-09-28 : 09:52:45

Yes, you can actually just leave the rowcount set to 1 until the end. I'd also recommend declaring @startdate and @enddate at the top and using those for the dates in your update statement. That way, the next time you want to run it, all you have to do is change those variables at the top.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-28 : 09:52:50

Eric,

It seems that you have more than 1 records per sales person per month and you only want to update one record ?

You have not tell us which record do you want to update. Whatever posted there all assume just one random record.

As said, please provide your table structure, with sample data and explain your requirement and what do you want to achieve.

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-28 : 10:04:44

[code]UPDATE x
SET x.Forcast = 69673.00
FROM dbo.[TransNet Corporation$Sales Commission] AS x
INNER JOIN (
SELECT [Sales Person],
MIN(DateCol) AS mi
FROM dbo.[TransNet Corporation$Sales Commission]
GROUP BY [Sales Person]
WHERE DateCol >= '20070901'
AND DateCol < '20071001'
) AS y ON y.[Sales Person] = x.[Sales Person] AND y.mi = x.DateCol[/code]

E 12°55'05.25"
N 56°04'39.16"

pharoah35
Yak Posting Veteran

81 Posts

Posted - 2007-09-28 : 10:27:37

Thanks again, You guys are the BEST !

Eric

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources