|
Today I was working on one query, found a strange issue in SQL server. Here I am
trying to reproduce the same issue.
Requirement: I was just trying to make it
a single row for the corresponding address table which has multiple addresses
(different address types) as a different row. Please note we have all the data
in Tables not in table variable. This is only for demonstration.
Sample table :
declare
@table Table (studentid
int, name
varchar(100),class varchar(5),
Homeadress
varchar(100),OfficeAddress varchar(100),LandLineAddress
varchar(100),PrimaryAddress varchar(100))
Sample daata :
insert
into @table values(1,'Rafeeq','10','','','','')
insert
into @table values(2,'Rayyan','8','','','','')
insert
into @table values(3,'Jasee','5','','','','')
insert
into @table values(4,'Azeema','3','','','','')
insert
into @table values(5,'Ameen','2','','','','')
Address table :
declare
@address table (studentid
int ,addressType
varchar(10),addressdetails varchar(100))
Some sample data
for address:
insert
into @address values(1,'Home','Home1')
insert
into @address values(1,'Office','Office1')
insert
into @address values(1,'LandLine','LandLine1')
insert
into @address values(1,'Primary','Primary1')
insert
into @address values(2,'Home','Home2222')
insert
into @address values(2,'Home','Home2')
insert
into @address values(2,'Office','Office2')
insert
into @address values(2,'LandLine','LandLine2')
insert
into @address values(2,'Primary','Primary2')
insert
into @address values(3,'Home','Home3')
insert
into @address values(3,'Office','Office3')
insert
into @address values(3,'LandLine','LandLine3')
insert
into @address values(3,'Primary','Primary3')
--select * from
@table as tbl
--inner join
@address as adr on adr.studentid = tbl.studentid
I was thinking
that it should update the records, but its updating only the first record of the
each join.
update
@table
set
Homeadress =
case when adr.addressType='Home' then adr.addressdetails else
Homeadress end
,
OfficeAddress = case
when adr.addressType='Office'
then adr.addressdetails
else OfficeAddress end
,
LandLineAddress = case
when adr.addressType='LandLine'
then adr.addressdetails
else LandLineAddress
end
,
PrimaryAddress = case
when adr.addressType='Primary'
then adr.addressdetails
else PrimaryAddress end
from
@table as tbl
inner
join @address as
adr on adr.studentid
= tbl.studentid
//Just tring to look
updated data.
select
* from @table
update
@table
set
Homeadress =
HomeAddr.addressdetails
,
OfficeAddress = OfficeAddr.addressdetails
,
LandLineAddress = LandLineAddr.addressdetails
,
PrimaryAddress = PrimaryAddr.addressdetails
from
@table as tbl
left
join
(select
* from @address
where addressType='Home')
as HomeAddr on
HomeAddr.studentid =
tbl.studentid
left
join
(select
* from @address
where addressType='Office')
as OfficeAddr on
OfficeAddr.studentid =
tbl.studentid
left
join
(select
* from @address
where addressType='LandLine')
as LandLineAddr on
LandLineAddr.studentid
= tbl.studentid
left
join
(select
* from @address
where addressType='Primary')
as PrimaryAddr on
PrimaryAddr.studentid =
tbl.studentid
//look here its updated
correctly.
select
* from @table
Found the problem.
If we will try to look the data here in join
you can see it looks like.
select
* from @table
as tbl
inner
join @address as
adr on adr.studentid
= tbl.studentid
The results of the first query look like this, that seems to be only update with
first record of the joined data.
If you will look at modified query then it will look good.
I am thinking to execute the same thing in oracle, will update soon. If you have
any comments please sent to bm@gmail.com for now.
|