Update 2 different tables in one query with 3 joins?

Question

I'm trying to do an update accross 2 tables which also has a view joins connected an additional two tables.

I get no syntax error but affected rows is zero so I'm wondering how the logic is meant to be written.. this is what i tried:

UPDATE resources R
    INNER JOIN cost C
    ON R.rid=R.rid          

    INNER JOIN users U
    ON R.uid=U.uid

    INNER JOIN money M
    ON M.bid=C.bid

   SET R.q = R.q - C.q, M.q = M.q - 1
   WHERE C.bid=? AND U.uid= ?

If i remove this part of the query M.q = M.q - 1 and the join for M it works fine but if i add it then no rows every get updated.

The query is support to remove the quantity totals from resources based on cost quantity AND remove 1 from the the table money field q.

How do i fix this =/ ?

possibly duplicate `http://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query` — Neeraj Dubey, Jul 25 '13 at 05:57
Not one of the answers in that link included joins..so i don't see how it would help in my case. — Sir, Jul 25 '13 at 05:58
you can refer this link `http://stackoverflow.com/questions/3707815/update-multiple-tables-in-a-single-update-statement-with-left-join`. If you are using MS-SQL then Sql 2008 have a `Merge` Statement by which you can perform multiple statement in single block.Refer this `http://stackoverflow.com/questions/2695116/update-multiple-table-column-values-using-single-query` — Neeraj Dubey, Jul 25 '13 at 06:05
Well i don't even know if i need a left join or not, hence why i'm asking for help. :) — Sir, Jul 25 '13 at 06:07
Also the those two links are not `mysql` so again not v.helpful for my situaiton. — Sir, Jul 25 '13 at 06:08
@Dave Can you provide relevant sample data in a tabular form or better yet sqlfiddle? — peterm, Jul 25 '13 at 06:16
@peterm i can - was thinking it would add to the confusion. Ill add them when i get home! Note though its only since the inclusion of table `money` that has stopped it from working so I have narrowed down the causes. — Sir, Jul 25 '13 at 06:18
@peterm http://sqlfiddle.com/#!2/26153/1 the result should show 9 not 10 for `q` — Sir, Jul 25 '13 at 06:38
@Dave See my answer. I blindly deduced your join conditionals from sample data. Not sure that completely correct. — peterm, Jul 25 '13 at 06:57

score 2 · Accepted Answer · answered Jul 25 '13 at 06:55

2

There are problems with JOIN conditions in your query. Right off the bat you have R.rid=R.rid which can't be right.

Now, deducing from your sample data IMHO your query should like this

UPDATE resources R JOIN costs C
    ON R.uid = C.rid JOIN users U
    ON R.uid = U.uid JOIN money M
    ON M.uid = C.rid
   SET R.q = R.q - C.q, M.q = M.q - 1
 WHERE C.bid = 3 
   AND U.uid = 1

Here is SQLFiddle demo

answered Jul 25 '13 at 06:55

peterm

91,357
15
148
157

it seems to be behaving correctly, may i ask why it is `joins` over `inner joins` – Sir Jul 25 '13 at 20:42
@Dave It's the same thing but less typing. If you prefer to state `INNER` explicitly it's absolutely fine. – peterm Jul 25 '13 at 20:49
if they are the same whats different to your query any my original ? – Sir Jul 25 '13 at 20:51
@Dave JOIN conditions are different `R.uid = C.rid`, `M.uid = C.rid` – peterm Jul 25 '13 at 21:10

Update 2 different tables in one query with 3 joins?

1 Answers1