update across multiple tables?

[jweissig]

hi,

is it possible to update via multiple tables using mysql? What i want to do is this.

i have 2 tables..

table agents
fk_bid
name

table brokers
name

what i want to do is say "update agents set fk_id = brokers.id where brokers.name = "example name";

is this possible, if not what would be possible. I though about a sub select but mysql does not have this? Any idea's?

• Justin

[jweissig]

i guess the example SQL statment should be..

UPDATE agent SET agent.fk_bid = broker.id WHERE agent.name = "example agent" AND broker.name = "example broker";

[jweissig]

i don't know how it is going to work so i am going to make a script to run through the database and basically do the following! This is an example for anyone else who wants to know!

mysql> select id from agent where name = "example agent";
+-----+
| id |
+-----+
| 894 |
+-----+
1 row in set (0.00 sec)

mysql> select id from broker where name = "example broker";
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)

mysql> update agent set fk_bid = 2 where id = 894;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql>