in a subselect query how do i refer to the id of the top level select statement?

Question

i wanted to automate my table population for testing purposes

i needed to edit some columns from a certain table but i must make sure that the values i put in that certain column does not simply come out of randomness
so the values actually comes from another table on a certain condition
so how do i do that? like here:

update table_one set `some_id`=(select some_id from another_table where another_table.primary_id=table_one.primary_id order by rand() limit 1)

so its something like my condition for the subselect query is that it should match the id of the current row i am updating.. i really forgot my sql now heee thanks for the answers though

Answer 1

You're almost there - all you need to do is qualify the column you're selecting in the subquery, so you know it comes from the correct table:

update table_one
set some_id=(
    select another_table.some_id
    from another_table
    where another_table.primary_id=table_one.primary_id
    order by rand()
    limit 1
)