How to update multiple rows in a table.

chakran

Hi, I have requirement to update the weekno and StartdayofWeek. By using the below query I can get weekno and StartdayofWeek but I need to insert/update these values in respective columns in the original table. Can any one help me. Select BugDate, DATEADD(wk, DATEDIFF(wk, 6,[BugDate]), 6) as startdayofweek, Datepart(wk,[BugDate]) + ((Datepart(year,[BugDate]) - 2008) * 52) as WeekNumber from [BugsDB_DefectVolume] Table: ID BugDate Weekno BugNo StartDayofWeek 111 2009-04-12 NULL 3 NULL 111 2009-04-19 NULL 1 NULL 111 2009-04-26 NULL 1 NULL 111 2009-05-03 NULL 1 NULL 111 2009-05-10 NULL 9 NULL 111 2009-05-17 NULL 9 NULL 111 2009-05-24 NULL 4 NULL 111 2009-05-31 NULL 2 NULL 111 2009-06-07 NULL 4 NULL Thanks.

geeeeeeeetha

hi, if both table having unique column,then u can use below query update MyTable set NtextColumn = ot.NtextColumn from dbo.MyTable join anotherDB.dbo.OtherTable ot on MyTable.KeyColumn = ot.KeyColumn

chakran

sorry there is no other table. We have only one table called "BugsDB_DefectVolume". In that single table we need to update weekno and Startdayofweek using Bugdate column...

geeeeeeeetha

hi, create one temporarytable... insert values to temporary table... and use previous post query... if i'm wrong plz excuse

Niladri_Biswas

Hi, Update the target table based on bug date Here is the example Just creating a dummy source table

declare @tblSource table(bugdate datetime)
insert into @tblSource
select '2009-04-12' union all
select '2009-04-19' union all
select '2009-04-26' union all
select '2009-05-03' union all
select '2009-05-10' union all
select '2009-05-17' union all
select '2009-05-24' union all
select '2009-05-31' union all
select ' 2009-06-07'

Your target table schema

create table tblTarget (id int,bugdate date,weekno int,bugno int,startdayofweek datetime)
insert into tblTarget
select 111,'2009-04-12',null,3,null union all
select 111,'2009-04-19',null,1,null union all
select 111,'2009-04-26',null,1,null union all
select 111,'2009-05-03',null,1,null union all
select 111,'2009-05-10',null,9,null union all
select 111,'2009-05-17',null,9,null union all
select 111,'2009-05-24',null,4,null union all
select 111,'2009-05-31',null,2,null union all
select 111,' 2009-06-07',null,4,null
select * from tblTarget

Output (Before updation)

id bugdate weekno bugno startdayofweek
111 2009-04-12 NULL 3 NULL
111 2009-04-19 NULL 1 NULL
111 2009-04-26 NULL 1 NULL
111 2009-05-03 NULL 1 NULL
111 2009-05-10 NULL 9 NULL
111 2009-05-17 NULL 9 NULL
111 2009-05-24 NULL 4 NULL
111 2009-05-31 NULL 2 NULL
111 2009-06-07 NULL 4 NULL

Next execute the query

;with cte as
(
select bugdate,
DATEADD(wk, DATEDIFF(wk, 6,bugdate), 6) as startdayofweek,
Datepart(wk,bugdate) + ((Datepart(year,bugdate) - 2008) * 52) as WeekNumber
from @tblSource
)
update tblTarget
set tblTarget.weekno = c.WeekNumber,tblTarget.startdayofweek = c.startdayofweek
from cte c
where c.bugdate = tblTarget.bugdate

Output(After Updation)

id bugdate weekno bugno startdayofweek
111 2009-04-12 68 3 2009-04-12 00:00:00.000
111 2009-04-19 69 1 2009-04-19 00:00:00.000
111 2009-04-26 70 1 2009-04-26 00:00:00.000
111 2009-05-03 71 1 2009-05-03 00:00:00.000
111 2009-05-10 72 9 2009-05-10 00:00:00.000
111 2009-05-17 73 9 2009-05-17 00:00:00.000
111 2009-05-24 74 4 2009-