WEB Nested MySQL Queries

Discussion in 'OT Technology' started by hank85, Sep 25, 2009.

  1. hank85

    hank85 sudo shred /dev/sda -f -v -z --iterations=6

    Joined:
    Jul 23, 2008
    Messages:
    4,360
    Likes Received:
    0
    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.
     
    Last edited: Sep 25, 2009
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    post it up in here
     
  3. hank85

    hank85 sudo shred /dev/sda -f -v -z --iterations=6

    Joined:
    Jul 23, 2008
    Messages:
    4,360
    Likes Received:
    0

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

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    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>
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    here's the exact query i ran on my test db:

    Code:
    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
    
     
  6. hank85

    hank85 sudo shred /dev/sda -f -v -z --iterations=6

    Joined:
    Jul 23, 2008
    Messages:
    4,360
    Likes Received:
    0
    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.
     
  7. hank85

    hank85 sudo shred /dev/sda -f -v -z --iterations=6

    Joined:
    Jul 23, 2008
    Messages:
    4,360
    Likes Received:
    0
    Ended up just scripting it.

    Thanks though.
     

Share This Page