CODEWITHSUNDEEP
sqlsql-server
John
John

Reputation: 15

SQl Update on Multiple Records

StartDate       Departmentid    DateID
Jan 2010        3               500
Febuary 2011    8               501
March 2012      2               503
April 2013      1               504

Update above where departmentID should have the values of DateID

above 2 columns are coming from different Tables: 

StartDate is from [DatesAvaliable]
DepartmentID is from [Persons]
DateID is from [DatesAvaliable]

The query used to build it is the following: 

select i.startdate,DepartmentID,i.id as DateID 
from persons p 
inner join substitutetable ppk on ppk.id = p.DepartmentID 
inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
where departmentID is not null

This Table [substitutetable] kind of makes a connection between the table, otherwise the records I get are not possible

How can i write an update query to update the above records

Where i can replace the departmentID values with DateID?

Upvotes: 0

Views: 72

Answers (2)

Kemal AL GAZZAH
Kemal AL GAZZAH

Reputation: 1037

You can also use cte (common table expression)

with cte as
(select p.departmentid,i.id from persons p 
    inner join substitutetable ppk on ppk.id = p.DepartmentID 
    inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
    where departmentID is not null)
update cte set departmentid=id;

Upvotes: 0

SS_DBA
SS_DBA

Reputation: 2423

If I'm following this correctly, which is confusing to begin with...

But if your select statement gets you what you need, then just turn your select statement into an update statement.

select i.startdate,DepartmentID,i.id as DateID 
from persons p 
inner join substitutetable ppk on ppk.id = p.DepartmentID 
inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
where departmentID is not null

** UPDATE **

Update P
Set DepartmentID = i.id
from persons p 
    inner join substitutetable ppk on ppk.id = p.DepartmentID 
    inner join DatesAvaliable i on i.id = ppk.DateAvaliableID 
    where departmentID is not null

Upvotes: 2

Related Questions