Nested MySQL Queries

Is anyone good with nested queries? I’m having trouble writing one up, wanted to know if someone could shoot me an IM.

UPDATE <table> SET <column> = (SELECT <column> FROM <other_table> ORDER BY rand()) WHERE id=<id from other_table>

Basically trying to get a piece of information from another table selected at random specified by id. I don’t know how I would get the UPDATE portion of the query to match up with the SELECT portion.

Updated first post. Let me know if I can clear it up any further.

Is anyone good with nested queries? I’m having trouble writing one up, wanted to know if someone could shoot me an IM.

UPDATE <table> SET <column> = (SELECT <column> FROM <other_table> ORDER BY rand()) WHERE id=<id from other_table>

Basically trying to get a piece of information from another table selected at random specified by id. I don’t know how I would get the UPDATE portion of the query to match up with the SELECT portion.

well, it looks like you’re trying to update a single record and you can’t update a column to be multiple values from your inner select, so you could probably do something like this:

UPDATE <table> SET <column> = (SELECT <column> FROM <other_table> ORDER BY rand() LIMIT 1) WHERE id=<id from other_table>

here’s the exact query i ran on my test db:

mysql> update current set book_id = (select id from books order by rand() limit 1) ;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

well, it looks like you’re trying to update a single record and you can’t update a column to be multiple values from your inner select, so you could probably do something like this:

UPDATE <table> SET <column> = (SELECT <column> FROM <other_table> ORDER BY rand() LIMIT 1) WHERE id=<id from other_table>

How can I get the WHERE id=<id from other_table> to coincide with the portion of the the select statement?

I want row 2156 to equal the same thing as id 2156 in current.

And you’re right about the multiple values, totally forgot to limit the statement.

Ended up just scripting it.

Thanks though.