Click here to Skip to main content
14,975,411 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
update two tables with join and where condition

i need two tables update and with 1 condition. Example table1 and table2 with 1 condition.

how write query?

any idea!

i get this error for my query.Here how to modified query?



SQL
update ps set 
PurposeOfVisit=@purposeofvisit,
   p.IsBanned=@isbanned
   from [PersonSessions] ps
   inner join person as p on p.id=ps.visitorid
   where ps.Id = @id


Here pass the value like that field.

SQL
update ps set
PurposeOfVisit='interview',
   p.IsBanned=false
   from [PersonSessions] ps
   inner join person as p on p.id=ps.visitorid
   where ps.Id = 4


how to update two tables join with where condition
Posted
Updated 18-Nov-12 23:25pm
v2
Comments
Maciej Los 17-Nov-12 9:59am
   
Do these queries don't worknig? Why? Please, be more specific!
pkarthionline 19-Nov-12 5:21am
   
i need two tables update
and with 1 condition.

any table1 and table2

how write query?
Sachin Gargava 19-Nov-12 7:40am
   
if "PurposeOfVisit" is from PersonSessions table and "IsBanned" is from person table this is also a way to do this please try this


BEGIN TRANSACTION

update ps set ps.[PurposeOfVisit]='Update purpose'
from [PersonSessions] ps
inner join person as p on p.id=ps.visitorid
where ps.Id =4



update ps set p.IsBanned='Updated Banned'
from [PersonSessions] ps
inner join person as p on p.id=ps.visitorid
where ps.Id =4

commit

hope you will Happy...

1 solution

i think it is not possible to update two tables with single update statement. You can create procedure and update multiple tables or create triggers. You can also use transactions and can make sure that both table data is updated correctly.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900