Multiple updates in same update statement

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 2005 Forums

Transact-SQL (2005)

Multiple updates in same update statement

Author

Topic

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-05 : 12:14:48

Hello i have a table like this

CREATE TABLE [dbo].[ANNUAL](
[SERIAL] [char](5) NOT NULL,
[START] [smalldatetime] NOT NULL,
[GROUP] [varchar](4) NULL,
[RW] [float] NULL,
[PR] [float] NULL,
[NU] [float] NULL,
[MU] [float] NULL
)

values are like

insert into ANNUAL select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, NULL, NULL union
select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, NULL, 2.35 union
select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, 0.95, NULL union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, NULL, 2.75 union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, NULL union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, NULL union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, NULL, 2.75 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.99, 3.26 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.94, 3.29 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union

i want to update NU and MU where it is NULL for a SERIAL to a rate from another SERIAL within the same GROUP where there is a rate. i do it separately for NU and MU like this

update A
set A.nu = B.nu
from (select * from ANNUAL where Start = '04/01/2011' and nu is null) A
inner join (select * from ANNUAL where Start = '04/01/2011' and nu is not null) B
on A.GROUP = B.GRoup
and A.PR = B.PR
and A.RW = B.RW

and then i do it for MU.
1. can i combine and do it one update statement
2. if there are more than one distinct rate of NU and/or MU for the same GROUP and START date, i do not want to update anything but want to pull it out 'SELECT' and review it e.g. for GROUP '0628'
3. can this be done in a function or sproc

thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 12:35:01

Don't you have a requirements issue here.
Look at the last 4 rows in the data you have given.
You would want to update the last two as they are null but you have two rows that are not null - which do you want to use for the update>?

==========================================
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.

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-05 : 12:47:50

In that case i did not want to update it?

from my requirement 2.

2. if there are more than one distinct rate of NU or MU for the same GROUP and START date, i do not want to update anything but i want to pull it out in a separate 'SELECT' and review it manually e.g. for GROUP '0628'

select '12345', '04/01/2011', '0628' , 1.252, 5.789,0.99, 3.26
union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.94, 3.29
union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:04:18

so what should be your output from above dataset?

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

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-05 : 14:07:38

Hi Visakh

This is the output i am looking for

select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, 0.95, 2.35 union
select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, 0.95, 2.35 union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.99, 3.26 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.94, 3.29 union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-05 : 14:12:58

select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, 0.95, 2.35 union all
select '12345', '04/01/2011', '0625' , 1.236498, 5.6465466, 0.95, 2.35 union all
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union all
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union all
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union all
select '12345', '04/01/2011', '0627' , 1.236445, 5.565, 0.97, 2.75 union all
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.99, 3.26 union all
select '12345', '04/01/2011', '0628' , 1.252, 5.789, 0.94, 3.29 union all
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL union all
select '12345', '04/01/2011', '0628' , 1.252, 5.789, NULL, NULL

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-05 : 14:21:11

Then you will have to deal with mu and nu separately. Can stil be a single update but the select will have to be a separate statement so why bother.

update a
set nu = case when b.numnu = 1 and a.nu is null then b.nu else a.nu end
, mu = case when b.nummu = 1 and a.mu is null then b.mu else a.mu end
from Annual a
join (select GROUP, PR, RW, nu = max(nu), mu = max(mu), numnu = count(nu), nummu = count(mu) from Annual group by GROUP, PR, RW) b
on a.GROUP = b.GROUP
and a.PR = b.PR
and a.RW = b.RW
where (a.nu is null and b.nu is not null and b.numnu = 1)
or (a.mu is null and b.mu is not null and b.nummu = 1)

==========================================
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 - 2011-07-05 : 14:22:26

seems like this


UPDATE t
SET t.NU= t1.NU
,t.MU=t1.MU
FROM Table t
INNER JOIN (SELECT SERIAL,GROUP,MAX(MU) AS MU,MAX(NU) AS NU,
            SUM(CASE WHEN MU IS NOT NULL AND NU IS NOT NULL THEN 1 ELSE 0 END) AS Cnt
            FROM Table 
            GROUP BY SERIAL,GROUP)t1
ON t1.SERIAL = t.SERIAL
AND t1.GROUP = t.GROUP
WHERE t1.Cnt = 0

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

jayram11
Yak Posting Veteran

97 Posts

Posted - 2011-07-05 : 14:43:48

Thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-05 : 14:45:20

w/c

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources