updating two tables with a single query

Question

Possible Duplicate:
MySQL, update multiple tables with one query

I'd like to know how to update two different tables with a single query, using MySql and C#.

Here is my query to read the data from the database.

string sql = @"SELECT clb.clube AS nome, clb.email AS email, clb.telefone AS 
telefone, usr.login AS login, usr.senha AS senha 
FROM clubes AS clb 
INNER JOIN usuarios AS usr ON usr.nome = clb.clube 
WHERE usr.login = @log";

Note that I get the data from usr and clb. Now, I'd like to update it. How can I do that?

http://dev.mysql.com/doc/refman/5.5/en/update.html – bonCodigo Jan 17 '13 at 20:03 — bonCodigo, Jan 17 '13 at 20:03

score 1 · Answer 1 · answered Jan 17 '13 at 20:06

1

You can join tables in the UPDATE statement:

UPDATE clubes AS clb INNER JOIN usuarios AS usr ON usr.nome = clb.clube
SET    clb.telefone = ...,
       usr.senha    = ...
WHERE  usr.login = @log

answered Jan 17 '13 at 20:06

eggyal

122,705
18
212
237

score 0 · Answer 2 · answered Jan 17 '13 at 20:06

0

There are a few ways to do this, but something like this should work for you:

UPDATE clubes, usuarios
SET clubes.clube='SomeVal', 
   usuarios.senha='SomeVal'
WHERE usuarios.nome = clubes.clube
  AND usr.login = 'SomeVal';

Good luck.

answered Jan 17 '13 at 20:06

sgeddes

62,311
6
61
83

I was unaware MySQL had this implemented, I stated in my Answer it was pulled from another post, I just forgot to put quotes around it. In my experience, normally these types of statements would fail. Even if you were targeting a view with the appropriate rules. – Chazt3n Jan 17 '13 at 20:11
No worries, just wanted to make you aware it couldn't be done. I did not give you a down vote since your SQL was correct and would work, not sure who did that. Good luck. – sgeddes Jan 17 '13 at 20:15
T'was my downvote, for "*I know you're using MySQL, but unfortunately the same rules apply*" (didn't leave a comment though, as yours already explained that). – eggyal Jan 17 '13 at 20:18

score 0 · Answer 3 · answered Jan 17 '13 at 20:06

0

Try thsi please:

UPDATE clubes clb
INNER JOIN usuarios AS usr 
ON (usr.nome = clb.clube) 
SET youcolumns....
WHERE usr.login = @log

answered Jan 17 '13 at 20:06

bonCodigo

14,268
1
48
91

Actually just the login does not update... Maybe because i'm using the login field TO update and in WHERE clause... what you htink ? – Ghaleon Jan 18 '13 at 19:19

updating two tables with a single query

3 Answers3