How to Fixed this Issue for Query Side [Resolved]

Posted by Jayakumars under Sql Server on 3/12/2017 | Points: 10 | Views : 276 | Status : [Member] [MVP] | Replies : 1
Hi

How to use Single update Query for both tables

CREATE TABLE MAS1
(
Id int primary key identity(1,1),
MasName varchar(50),
Tempid int,
RefTempId int
)

CREATE TABLE MAS2
(
Id int primary key identity(1,1),
Tempid int,
RefTempid int
)


Insert into MAS1 values('John',1,2)
Insert into MAS1 values('John',3,5)
Insert into MAS1 values('John',8,11)

Insert into MAS2 values(1001,2001)
Insert into MAS2 values(3001,5001)
Insert into MAS2 values(8001,1001)


select * from MAS1
select * from MAS2


--I need single update Query but Both table column


My Query This
===================
UPDATE mas1 SET  mas1.Tempid=mas2.Tempid
,mas1.RefTempid=mas2.RefTempid, mas2.RefTempid=mas1.RefTempId
FROM MAS1 mas1 JOIN MAS2 mas2 ON mas1.id=mas2.id
AND mas1.id=1


I tried in this Query but i meet this error any one fixed this

The multi-part identifier "mas2.RefTempid" could not be bound.

Mark as Answer if its helpful to you


Responses

Posted by: Rajnilari2015 on: 3/13/2017 [Member] [Microsoft_MVP] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
@Jayakumars Sir, logically speaking, it does not make sense. Because, you are trying to update the same field of Mas2 table by which you have updated the Mas1. So the value will remain same. That's the cause of the error.

Incase, however, you need to again update Mas2 by Mas1, then perform another Update like

UPDATE mas2 
SET
mas2.RefTempid=mas1.RefTempid
FROM MAS2 mas2
JOIN MAS1 mas1 ON mas2.id=mas1.id


/*
Id	Tempid	RefTempid
1 1001 2001
2 3001 5
3 8001 11

*/

--
Thanks & Regards,
RNA Team

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response