How to update Records following Situation [Resolved]

Posted by Jayakumars under C# on 3/11/2017 | Points: 10 | Views : 269 | Status : [Member] [MVP] | Replies : 3
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

--ex:set MAS1.Tempid=MAS2.Tempid,Mas1.RefTempid=Mas2.RefTempid
-- where mas1.id=mas2.id and mas1.id=1

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

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

Up
0
Down
@Jayakumars Sir,
Try this

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


/*

Id	MasName	Tempid	RefTempId
1 John 1001 2001
2 John 3 5
3 John 8 11



*/

--
Thanks & Regards,
RNA Team

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

Posted by: Jayakumars on: 3/12/2017 [Member] [MVP] Bronze | Points: 25

Up
0
Down
hi
Rajnilari2015

Please read my question like this

--I need single update Query but Both table column


I have mention just sample Query thats it

I need both table field need to update like this


can you alter this Query

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

But i meet this error how to fixed this?


My Error this

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

Mark as Answer if its helpful to you

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

Login to post response