Updating problem - 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

Updating problem

Author

Topic

j0shua
Starting Member

40 Posts

Posted - 2008-10-28 : 03:15:24

Hi, i have an sql statement. It works, however i find it long and tedious. Is there a better approach for this SQL statement? I am updating a table that has fixed rows.

here is my sql statement. thank you.


DECLARE @cbxJanuary As bit
DECLARE @cbxFebruary As bit
DECLARE @cbxMarch As bit
DECLARE @cbxApril As bit
DECLARE @cbxMay As bit
DECLARE @cbxJune As bit
DECLARE @cbxJuly As bit
DECLARE @cbxAugust As bit
DECLARE @cbxSeptember As bit
DECLARE @cbxOctober As bit
DECLARE @cbxNovember As bit
DECLARE @cbxDecember As bit

SET @cbxJanuary =  0
SET @cbxFebruary = 0
SET @cbxMarch = 0
SET @cbxApril = 0
SET @cbxMay = 0
SET @cbxJune = 0


UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxJanuary
WHERE fcMonth = 'January'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxFebruary
WHERE fcMonth = 'February'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxMarch
WHERE fcMonth = 'March'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxApril
WHERE fcMonth = 'April'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxMay
WHERE fcMonth = 'May'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxJune
WHERE fcMonth = 'June'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 03:43:20

[code]UPDATE dbo.tPeriodRestrictions
SET fcOpen = 0
WHERE fcMonth IN ( 'January','February','March','April','May','June')[/code]

j0shua
Starting Member

40 Posts

Posted - 2008-10-28 : 03:58:34

quote:

Originally posted by visakh16

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = 0
WHERE fcMonth IN ( 'January','February','March','April','May','June')

Thank you for the info, however, what if the parameters are user encoded? the status can be 1 or 0?

like this one.

quote:

create procedure usp_PeriodRestrictions
as
DECLARE @cbxJanuary As bit
DECLARE @cbxFebruary As bit
DECLARE @cbxMarch As bit
DECLARE @cbxApril As bit
DECLARE @cbxMay As bit
DECLARE @cbxJune As bit
DECLARE @cbxJuly As bit
DECLARE @cbxAugust As bit
DECLARE @cbxSeptember As bit
DECLARE @cbxOctober As bit
DECLARE @cbxNovember As bit
DECLARE @cbxDecember As bit

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxJanuary
WHERE fcMonth = 'January'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxFebruary
WHERE fcMonth = 'February'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxMarch
WHERE fcMonth = 'March'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxApril
WHERE fcMonth = 'April'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxMay
WHERE fcMonth = 'May'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxJune
WHERE fcMonth = 'June'
.
..
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:01:39

you meant @cbxJanuary to @cbxJune?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:07:15

it will be like

UPDATE dbo.tPeriodRestrictions 
SET fcOpen =CASE WHEN  fcMonth ='January'  THEN @cbxJanuary 
                 WHEN  fcMonth ='February' THEN @cbxFebruary 
                 WHEN  fcMonth ='March'    THEN @cbxMarch 
....
                 WHEN  fcMonth ='December'    THEN @cbxDecember
            END 
WHERE fcMonth IN ( 'January','February','March','April','May','June'...,'December')

j0shua
Starting Member

40 Posts

Posted - 2008-10-28 : 04:09:19

actually to december, i just posted a part of the whole statement.

this goes something like this"


create procedure usp_PeriodRestrictions
as
DECLARE @cbxJanuary As bit
DECLARE @cbxFebruary As bit
DECLARE @cbxMarch As bit
DECLARE @cbxApril As bit
DECLARE @cbxMay As bit
DECLARE @cbxJune As bit
DECLARE @cbxJuly As bit
DECLARE @cbxAugust As bit
DECLARE @cbxSeptember As bit
DECLARE @cbxOctober As bit
DECLARE @cbxNovember As bit
DECLARE @cbxDecember As bit

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxJanuary
WHERE fcMonth = 'January'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxFebruary
WHERE fcMonth = 'February'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxMarch
WHERE fcMonth = 'March'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxApril
WHERE fcMonth = 'April'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxMay
WHERE fcMonth = 'May'

UPDATE dbo.tPeriodRestrictions
SET fcOpen = @cbxJune
WHERE fcMonth = 'May'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxJune
WHERE fcMonth = 'June'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxJuly
WHERE fcMonth = 'July'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxAugust
WHERE fcMonth = 'August'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxSeptember
WHERE fcMonth = 'September'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxOctober
WHERE fcMonth = 'October'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxNovember
WHERE fcMonth = 'November'

UPDATE dbo.tPeriodRestrictions 
SET fcOpen = @cbxDecember
WHERE fcMonth = 'December'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:11:59

[code]create procedure usp_PeriodRestrictions
as
DECLARE @cbxJanuary As bit
DECLARE @cbxFebruary As bit
DECLARE @cbxMarch As bit
DECLARE @cbxApril As bit
DECLARE @cbxMay As bit
DECLARE @cbxJune As bit
DECLARE @cbxJuly As bit
DECLARE @cbxAugust As bit
DECLARE @cbxSeptember As bit
DECLARE @cbxOctober As bit
DECLARE @cbxNovember As bit
DECLARE @cbxDecember As bit

UPDATE dbo.tPeriodRestrictions
SET fcOpen =CASE WHEN fcMonth ='January' THEN @cbxJanuary
WHEN fcMonth ='February' THEN @cbxFebruary
WHEN fcMonth ='March' THEN @cbxMarch
....
WHEN fcMonth ='December' THEN @cbxDecember
END
WHERE fcMonth IN ( 'January','February','March','April','May','June'...,'December')[/code]
not sure where you're setting values for bit variables though

j0shua
Starting Member

40 Posts

Posted - 2008-10-28 : 04:35:34

ok i will try this one!! lots of thanksss!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-28 : 04:37:39

quote:
Originally posted by j0shua

ok i will try this one!! lots of thanksss!!

you're welcome

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-28 : 05:14:21

[code]UPDATE pr
SET pr.fcOpen = w.fcOpen
FROM dbo.tPeriodRestrictions AS pr
INNER JOIN (
SELECT 'January' AS fcMonth, @cbxJanuary AS fcOpen UNION ALL
SELECT 'February', @cbxFebruary UNION ALL
SELECT 'March', @cbxMarch UNION ALL
SELECT 'April', @cbxApril UNION ALL
SELECT 'May', @cbxMay UNION ALL
SELECT 'June', @cbxJune UNION ALL
SELECT 'July', @cbxJuly UNION ALL
SELECT 'August', @cbxAugust UNION ALL
SELECT 'September', @cbxSeptember UNION ALL
SELECT 'October', @cbxOctober UNION ALL
SELECT 'November', @cbxJNovember UNION ALL
SELECT 'December', @cbxDecember
) AS w ON w.fcMonth = pr.fcMonth[/code]

E 12°55'05.63"
N 56°04'39.26"

j0shua
Starting Member

40 Posts

Posted - 2008-10-28 : 06:36:35

Wow it works!!! This is so great information!!! GREAT THANKS!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources