CODEWITHSUNDEEP
mysql
Dusan
Dusan

Reputation: 3488

mysql query to update data in table using 2 tables

i am trying to build a simple cms system. I have 2 tables:

table pages:

id  |   index   |   lang
-------------------------------
1   |   9       |   1
2   |   10      |   1
3   |   11      |   1
4   |   0       |   2
5   |   0       |   2
6   |   0       |   2

table pairs:

id1 |   id2
------------
1   |   4
2   |   5
3   |   6

What i'm trying to do, is to update the index in pages which have lang=2 to the same number that have lang=1 and are their paired pages according to the other table.

In the above example i need to update pages 4,5,6 and set their indexes to 9,10,11

Is it possible to do this in 1 query? Thanks.

Upvotes: 2

Views: 116

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562971

I'd do this with a multi-table update:

UPDATE pages AS l2
INNER JOIN pairs AS p ON l2.id = p.id2
INNER JOIN pages AS l1 ON l1.id = p.id1
SET l2.index = l1.index
WHERE l2.lang = 2;

Upvotes: 1

elixenide
elixenide

Reputation: 44851

Try this:

UPDATE pages
SET index = (SELECT id1 FROM pairs WHERE pages.id = pairs.id2)
WHERE updateTbl.index = 0

(Edited; misread the question)

Upvotes: 0

Related Questions