please solve this query - SQL Server Forums

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

please solve this query

Author

Topic

nisar
Starting Member

29 Posts

Posted - 2007-04-24 : 22:43:50

hi anyone can please solve this query

I have table1 like this

locationid locationname
1 london
2 bombay

and in table2 like this

personname fromlocation tolocation
john 1 2
ryan 2 3

now i want to udpated table 2's from location and tolocation colum with all the all related value in table1

Regards
Monika

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-04-24 : 22:56:29

I don't believe we have enough information to write the actual query, but this should get you started. You use an update with a join.

UPDATE t2
SET SomeColumn1 = t1.SomeColumn1, SomeColumn2 = t1.SomeColumn2
FROM Table2 t2
INNER JOIN Table1 t1
ON t2.ColumnA t1.ColumnA

Tara Kizer
http://weblogs.sqlteam.com/tarad/

nisar
Starting Member

29 Posts

Posted - 2007-04-24 : 23:13:43

thanks please look this

table1

locationid====== locationname
1================delhi
2================london

table2
personname ====== from location === to location
john==============1(int)============2(int)

now i want to update table2 like this

personname ====== from location === to location
john============== delhi============ london

for 40 thousand records

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-04-24 : 23:28:25

If the fromlocation and tolocation columns in table2 are integer columns, how would you update them with character values?

In any case, this seems pointless, since you can just join table2 to table1 to get the from and to location names.

CODO ERGO SUM

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-04-25 : 04:45:21

As Michael told u can not update since the field is int type ....if u want the result as u mention try this....

declare @tt table (locid int , locname varchar(20))
insert into @tt
select 1, 'delhi' union
select 2, 'london'

declare @ss table (person varchar(50), frmloc int , toloc int)

insert into @ss
select 'john',1,2 union
select 'raja', 2,1 union

select a.person, max(case when a.frmloc = b.locid then b.locname end) as fromloc, max(case when a.toloc = b.locid then b.locname end) as Toloca from @ss a ,@tt b
group by a.person

----OR

select person, (select locname from @tt where locid = a.frmloc) as Frmloc,
(select locname from @tt where locid = a.toloc) as Toloc from @ss a

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources