How to update Records following Situation [Resolved]

Posted by Jayakumars under C# on 3/11/2017 | Points: 10 | Views : 934 | Status : [Member] [MVP] | Replies : 3

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

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

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

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

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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

Kumaraspcode2009@gmail.com

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

Latest Posts