update two tables with join and where

Question

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

pkarthionline

Updated 18-Nov-12 23:25pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Om Prakash Pant · Answer 1 · 2012-11-18T23:59:00

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.

Posted 18-Nov-12 23:59pm

Om Prakash Pant