how to update records with data from another table

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

how to update records with data from another table

Author

Topic

lbdbm
Starting Member

3 Posts

Posted - 2011-09-20 : 02:13:30

i want to update table2.message based on the criteria of table1.name. for example, all records named John will be updated with 'Msg1' in table 2.message. Im using MS SQL 2000 and below is the scenario.

table1 columns
ID
Name

table2 columns
ID
Message

Select a.Id, a.name, b.message
from table1 a, table2 b
where a.id =b.id

a.id a.name b.message
1 John Msg1
2 Steve Msg2
3 Scott Msg3
4 John NULL - update b.message to 'Msg1'
5 Steve NULL - update b.message to 'Msg2'
6 Scott NULL - update b.message to 'Msg3'
7 John NULL - update b.message to 'Msg1'
8 Steve NULL - update b.message to 'Msg2'

If i will update the record per name i am using the query below and i am pre-selecting all the existing names.

update table2 b
set b.message=(Select top 1 b.message
from table1 a, table2 b
where a.id =b.id
and a.name ='John')
where b.id in (select a.id
from table1 a, table2 b
where a.id = b.id
and b.message is null
and a.name ='John')

do you have suggestion on how to update this in bulk without preselecting all the names?

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-09-20 : 02:26:00

Use CASE Expressions in the UPDATE statement

--Ranjit

lbdbm
Starting Member

3 Posts

Posted - 2011-09-22 : 03:51:37

Hi! thanks for your reply, can you show me how to do it? my problem is - i have thousands of records and i don't know all the names as they are dynamic new records are being added regularly.

quote:
Originally posted by Ranjit.ileni

Use CASE Expressions in the UPDATE statement

--Ranjit

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-09-23 : 05:36:18

Try this

select t1.id,t2.name,t2.message from table1 as t1 inner join
(
Select a.name, max(b.message)
from table1 a, table2 b
where a.id =b.id
group by a.name
) as t2
on t1.name=t2.name

Madhivanan

Failing to plan is Planning to fail

lbdbm
Starting Member

3 Posts

Posted - 2011-09-26 : 05:37:50

Hi! thanks for the reply. here's the update stmt i constructed.

UPDATE u
SET u.message = t3.t3msg
FROM table2 u
INNER JOIN (select a.id t2id, a.name t2name
from table1 a) as t2 ON U.id = t2.t2id
INNER JOIN (select b.name t3name, max(c.message) t3msg
from table1 c, table2 c
where b.id = c.id
group by c.message) as t3 on t2.t2name =t3.t3name
WHERE U.message is null

quote:
Originally posted by madhivanan

Try this

select t1.id,t2.name,t2.message from table1 as t1 inner join
(
Select a.name, max(b.message)
from table1 a, table2 b
where a.id =b.id
group by a.name
) as t2
on t1.name=t2.name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources