Update 1 column with data from another column

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

Update 1 column with data from another column

Author

Topic

wiltjer
Starting Member

11 Posts

Posted - 2007-03-12 : 11:26:48

Hello,

I have a problem i'v been searching all day but i can't find an answer anywhere maybe someone here can help.
What I want to do is give a column in a table the same value as another column from the same table. For example:
Table:Requests
A request has a relatedrequestId wich links another request to it. Now I want the date from the linked request in the date from the master request. Because all the master requests date's are empty and i want them to have the date from the linked request.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-12 : 11:32:22

Our responses will be more accurate, if you could provide table structure and sample data.

Something like this should work:

Update t1
set datecol = (select datecol from table t2 where t2.id = t1.relatedrequestId)
From Table t1
Where t1.requesttype = 'master'

Update t1
set datecol = t2.datecol
From table t1 join table t2
on t2.id = t1.relatedrequestId
and t1.requesttype = 'master'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 11:36:23

Here's another example,

create table #Requests
(
RequestID int,
LinkedRequestID int,
RequestDate datetime
)
insert #Requests
select 1, null, null union all
select 2, null, null union all
select 3, null, null union all
select 4, null, null union all
select 5, 1, '1/1/2007' union all
select 6, 2, '1/2/2007' union all
select 7, 3, '1/3/2007' union all
select 8, 4, '1/4/2007'

select * from #Requests

update #Requests
set RequestDate = l.RequestDate
from #Requests
inner join #Requests l on #Requests.RequestID = l.LinkedRequestID

select * from #Requests

drop table #Requests

wiltjer
Starting Member

11 Posts

Posted - 2007-03-12 : 11:42:16

Oke the table name is actually Absences.

I had something like this:
SELECT
a.enddate AS master_enddate,
a1.enddate AS related_enddate

FROM absences AS a

INNER JOIN absences AS a1 ON a.relatedrequestid=a1.ID

UPDATE a

SET master_enddate=related_enddate
But this obviously doesn't work...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 11:47:30

So just put your table and column names into the code I gave you...

wiltjer
Starting Member

11 Posts

Posted - 2007-03-12 : 11:53:40

Thank you for the quick reply's I will go and see if it works now ;)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-12 : 11:55:15

[code]UPDATE a
SET enddate=a1.enddate
FROM absences AS a INNER JOIN absences AS a1
ON a.relatedrequestid=a1.ID[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources