How to Fixed this Issue for Query Side [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	@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

@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

Latest Posts