How can we update two different tables with single sql query?

Question

How can we update two tables with single sql query?

Suppose I have two tables

1. Employee

empid  empname     salary
00001   Vijay      100000
00002   Alex       200000
00003   Jennifer   300000


2. Institute

regno    State        Country
123ab    Texas          USA
231nf    California     USA

So how can we update both tables lets say I want to update "salary" field of "empid" 00001 of "Employee" table and the "State" field of "regno" 123ab of Institute table.

You can't. `update` targets a single table only. use a transaction and two separate queries. the transaction will effective turn the two queries into a single one. — Marc B, Oct 17 '13 at 18:48
http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — Brian, Oct 17 '13 at 18:49
http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query — Gobo, Oct 17 '13 at 18:49
I'll write it for money. If you show effort, I'll help for free ;) — Sterling Archer, Oct 17 '13 at 18:54
Why using a single query to update two different tables which are related to each other? It needs two query and it is better to keep it that way. — heretolearn, Oct 17 '13 at 18:55
@sshekhar i know with two queries its damn simple but can u help me with my question concerned ? — Vijay Chauhan, Oct 17 '13 at 18:57

score 3 · Answer 1 · answered Oct 17 '13 at 18:49

3

if it's related data, you can probably can do a JOIN.
if it's not, you are probably looking not for 1 query, but 1 transaction
if you still need it to be 1 call, define a procedure

answered Oct 17 '13 at 18:49

Wrikken

69,272
8
97
136

thanks for the information but mate can we use some other way to find the solution apart from Transaction and Procedures ? – Vijay Chauhan Oct 17 '13 at 19:03
1

@VijayChauhan By now you should be aware that there's no way to do it in a single SQL instruction... that's what transactions are for – Barranka Oct 17 '13 at 19:07
3

Honestly, trying to avoid transactions or procedures, having unrelated data, and still insisting on a single query, smells a **lot** like asking for instructions on how to do an SQL-injection attack after having found a security flaw. It may not be it, but at the very least is see no _valid_ use for the requirements any other way. – Wrikken Oct 17 '13 at 19:17

score 1 · Answer 2 · answered Oct 17 '13 at 18:50

1

BEGIN TRANSACTION
query1;
query2;
COMMIT

answered Oct 17 '13 at 18:50

Mihai

26,325
7
66
81

score 0 · Answer 3 · answered Oct 17 '13 at 18:48

0

You could create a stored procedure which updates Employee and then updates Institute so then your single query would be:

exec spUpdateEmployeeTableAndInstituteTable

answered Oct 17 '13 at 18:48

Jack Marchetti

15,536
14
81
117

Can we do that without using TRANSACTION? – Vijay Chauhan Oct 17 '13 at 18:55

score 0 · Answer 4 · answered Oct 17 '13 at 18:56

0

It actually can be done in one query:

UPDATE Employee e, Institute i
SET e.salary = <new salary>,
    i.state = <new state>
WHERE e.empid = '00001'
AND i.regno = '123ab'

answered Oct 17 '13 at 18:56

Barmar

741,623
53
500
612

U hav used JOIN operation but the tables are not related to each other so how can we have JOIN operation in that case ? – Vijay Chauhan Oct 17 '13 at 19:01
It just does a cross-product of the rows matching each condition. – Barmar Oct 17 '13 at 19:01

How can we update two different tables with single sql query?

4 Answers4