Click here to Skip to main content
12,398,000 members (45,512 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL-Server
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?



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.

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 17-Nov-12 3:17am
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

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 19 Nov 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100