Update 2 table that have joined via key

bono56 · #1 (**permalink**) 05-30-07, 09:31

i have 2 table that have joied via a key, now i want to UPDATE fields in 2 table, is there any way for updating 2 joined table?
i can do this by IN synatx like this:

Code:

UPDATE table 2 SET blabla='yes'
WHERE id IN (SELECT id FROM table1)

but in thousands of record, it cause hangin.

aschk · #2 (**permalink**) 05-30-07, 10:27

You're updating thousands of records at once with the same value???

guelphdad · #3 (**permalink**) 05-30-07, 13:52

This would be faster from what I've read. Also note you should have the id columns indexed.

Code:

UPDATE table1, table2
SET table2.blabla='yes'
WHERE table1.id=table2.id

aschk · #4 (**permalink**) 05-31-07, 03:52

Just a quick question. If there is referential integrity here (which there ought to be as you have implied the use of a key between the two tables) then in which direction does this integrity run?

it is table1 has (many) table2
or table2 has (many) table1
?

From what I can see you are updating table2 based on the fact that it contains the same ID as an ID that exists in table1. However (and i'm only guessing here) when will table2 NOT have the an ID that exists in table1 if they share the same key???

Internet Services

Web Development

Digital Marketing

Consumer Tech