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

Batch update

Author

Topic

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-01-05 : 22:17:10

Hi,
here the below two query is updating the same table.Instead of breaking into two statement,how to run in single statement(single update statement by updating two query)

update elstatement
set fd1=0,fd2=24,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26 where (year(pfdate)='2006' and month(pfdate)=2)"

update elstatement
set fd1=0,fd2=0,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26"
where (year(pfdate)='2006' and month(pfdate)=3)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-06 : 00:01:00

Provided that pfdate is of datetime datatype, this can work

Update.......
where pfdate>='2006-02-01' and pfdate<='2006-03-31'

Madhivanan

Failing to plan is Planning to fail

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-06 : 00:53:42

or:

Update.......
where pfdate between '2006-02-01' and '2006-03-31'

www.elsasoft.org

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-01-06 : 01:37:44

hi ,
update elstatement set a=12
where pfdate between '2006-01-01' and '2006-01-31'

update elstatement set b=20
where pfdate between '2006-02-01' and '2006-02-28'

two different condt,two diff update in single update statement
How to do this in single update???????????

quote:
Originally posted by sent_sara

Hi,
here the below two query is updating the same table.Instead of breaking into two statement,how to run in single statement(single update statement by updating two query)

update elstatement
set fd1=0,fd2=24,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26 where (year(pfdate)='2006' and month(pfdate)=2)"

update elstatement
set fd1=0,fd2=0,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26"
where (year(pfdate)='2006' and month(pfdate)=3)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-06 : 01:56:32

Why do you want single update?


Update elstatement 
set b=CASE 
          WHEN pfdate between '2006-01-01' and '2006-01-31' then 12 
          WHEN pfdate between '2006-02-01' and '2006-02-28' then 20
      ELSE b
      END

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-01-06 : 02:16:40

"pfdate <= '2006-03-31'"

Not that this will miss any PFDATE on 31-Mar-2006 which has a time component. This might be safer:

pfdate < '2006-04-01'

Note also that you should NOT include hyphens in string dates - the unambiguous format is YYYYMMDD, so:

pfdate < '20060401'

and

pfdate between '2006-01-01' and '2006-01-31'"

becomes

pfdate >= '20060101' and pfdate < '20060201'

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-01-06 : 02:26:31

Good Points Kristen

Madhivanan

Failing to plan is Planning to fail

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2007-01-06 : 11:26:32

quote:
update elstatement set a=12
where pfdate between '2006-01-01' and '2006-01-31'

update elstatement set b=20
where pfdate between '2006-02-01' and '2006-02-28'

Sent_Sara,

I don't know... maybe I'm reading this wrong... could it be that the actual definition of this problem is that you want to update column "A" for one month and that, no matter what that month is, you want to update column "B" for the following month?

If so, then "one" way to do this would be a mix of what everyone has posted to account for any dates with times and an "oolie" or two...

DECLARE @BaseDate DATETIME
    SET @BaseDate = '2006-01-01' --Note: Can be ANY day of the month!

 UPDATE ElStatement
    SET A = CASE 
               WHEN PfDate >= DATEADD(mm,DATEDIFF(mm,0,@BaseDate)  ,0) --1st of BaseDate Month   --Ex: 1st of Jan
                AND PfDate <  DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+1,0) --1st of BaseDate Month+1 --Ex: 1st of Feb
               THEN 12
               ELSE A  --Keeps original value of "A"
             END,
        B = CASE 
               WHEN PfDate >= DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+1,0) --1st of BaseDate Month+1 --Ex: 1st of Feb
                AND PfDate <  DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+2,0) --1st of BaseDate Month+2 --Ex: 1st of Mar
               THEN 20
               ELSE B  --Keeps original value "B"
             END
  WHERE PfDate >= DATEADD(mm,DATEDIFF(mm,0,@BaseDate)  ,0) --1st of BaseDate Month   --Ex: 1st of Jan
    AND PfDate <  DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+2,0) --1st of BaseDate Month+2 --Ex: 1st of Mar

Now, something that you need to be aware of... I put "one" in quotes because doing something like this in a single UPDATE is a very bad idea (that's why everyone keeps asking you WHY you want to do this and you NEED to tell them)... even if the value for "A" doesn't actually change, the column has still been updated and any triggers that fire when "A" is updated will, in fact, fire even though the value of the column may not have changed. If you do it this way, you must also make sure that any triggers (especially audit triggers and replication triggers) you have, also determine if the values in the INSERTED and DELETED tables of the trigger have actually changed.

The proper way to do this would be as follows (keeps triggers from firing unnecessarily)... and, YES... it uses two updates...

DECLARE @BaseDate DATETIME
    SET @BaseDate = '2006-01-01' --Note: Can be ANY day of the month!

 UPDATE ElStatement
    SET A = 12
  WHERE PfDate >= DATEADD(mm,DATEDIFF(mm,0,@BaseDate)  ,0) --1st of BaseDate Month   --Ex: 1st of Jan
    AND PfDate <  DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+1,0) --1st of BaseDate Month+1 --Ex: 1st of Feb

 UPDATE ElStatement
    SET B = 20
  WHERE PfDate >= DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+1,0) --1st of BaseDate Month+1 --Ex: 1st of Feb
    AND PfDate <  DATEADD(mm,DATEDIFF(mm,0,@BaseDate)+2,0) --1st of BaseDate Month+2 --Ex: 1st of Mar

--Jeff Moden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-06 : 12:08:02

According to original post

update	elstatement 
set	fd1 = 0,
	fd2 = CASE WHEN month(pfdate) = 2 then 24 else 0 end,
	fd3 = 27,
	fd4 = 25,
	fd5 = 25,
	fd6 = 26,
	fd7 = 26,
	fd8 = 26,
	fd9 = 26,
	fd10 = 22,
	fd11 = 26,
	fd12 = 26 
where	pfdate >= '20060201'
	and pfdate < '20060401'

Peter Larsson
Helsingborg, Sweden

sent_sara
Constraint Violating Yak Guru

377 Posts

Posted - 2007-01-08 : 02:48:03

Txs JeFF MODN,PESO and all other for giving the solution

quote:
Originally posted by sent_sara

Hi,
here the below two query is updating the same table.Instead of breaking into two statement,how to run in single statement(single update statement by updating two query)

update elstatement
set fd1=0,fd2=24,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26 where (year(pfdate)='2006' and month(pfdate)=2)"

update elstatement
set fd1=0,fd2=0,fd3=27,fd4=25,fd5=25,fd6=26,fd7=26,fd8=26,fd9=26,fd10=22,fd11=26,fd12=26"
where (year(pfdate)='2006' and month(pfdate)=3)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources