Professional Web Applications Themes

query syntax to replace column value from another table? - MySQL

I have two MySQL tables, and in a query, I would like to replace the value of a column from one table with that of a column from another table. I can't figure out whether this is a join (or what type), or something else. For example, if you have two tables: people: id | name nicknames: id | nickname .... and a pseudo-query: select people.id, people.name {but instead, return correlating value of nicknames.nickname}, from people, nicknames where people.id = nicknames.id; How do you get the {} stuff?...

  1. #1

    Default query syntax to replace column value from another table?

    I have two MySQL tables, and in a query, I would like to replace the
    value of a column from one table with that of a column from another
    table. I can't figure out whether this is a join (or what type), or
    something else.

    For example, if you have two tables:
    people: id | name
    nicknames: id | nickname

    .... and a pseudo-query:
    select people.id,
    people.name {but instead, return correlating value of
    nicknames.nickname},
    from people, nicknames
    where people.id = nicknames.id;

    How do you get the {} stuff?

    John Guest

  2. #2

    Default Re: query syntax to replace column value from another table?

    John DeStefano wrote: 

    select
    people.id,
    people.name,
    nicknames.nickname

    from
    people
    join nicknames
    on
    people.id = nicknames.id

    or if you'd like to stick to what you already have:

    select
    people.id,
    people.name,
    nicknames.nickname

    from
    people,
    nicknames
    where people.id = nicknames.id

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  3. #3

    Default Re: query syntax to replace column value from another table?

    Yes, that works perfectly. Thank you!

    But what if you need more than one column "replacement" from the same
    two tables? If I add some more columns to "people":
    mysql> select * from people;
    +----+---------------+-------------+--------------+-------------+
    | id | name | best_friend | worst_friend | worst_enemy |
    +----+---------------+-------------+--------------+-------------+
    | 1 | James Robert | 2 | 3 | 4 |
    | 2 | Lawrence | 1 | 3 | 4 |
    | 3 | Odorless | 2 | 4 | 1 |
    | 4 | William Small | 3 | 1 | 2 |
    +----+---------------+-------------+--------------+-------------+
    4 rows in set (0.00 sec)

    mysql> select * from nicknames;
    +----+----------+
    | id | nickname |
    +----+----------+
    | 1 | Jim Bob |
    | 2 | Screech |
    | 3 | Stinky |
    | 4 | Tiny |
    +----+----------+
    4 rows in set (0.00 sec)


    If I try to set more than one friend/enemy column equal to
    nicknames.id, in order to display the nickname value instead of the ID
    number, the query fails. Can you do multiple joins to do this?

    John Guest

  4. #4

    Default Re: query syntax to replace column value from another table?

    John DeStefano wrote: 
    > +----+---------------+-------------+--------------+-------------+ 
    > +----+---------------+-------------+--------------+-------------+
    > 4 rows in set (0.00 sec)
    >
    > mysql> select * from nicknames;
    > +----+----------+ 
    > +----+----------+ 
    > +----+----------+
    > 4 rows in set (0.00 sec)
    >
    >
    > If I try to set more than one friend/enemy column equal to
    > nicknames.id, in order to display the nickname value instead of the ID
    > number, the query fails. Can you do multiple joins to do this?[/ref]

    You are not setting a "column" equal to nickname.id, you are joining 2 rows
    where the id's match.

    Try this to see what I mean:
    SELECT
    *
    FROM `people`
    JOIN `nicknames` USING(`id`)


    Paul Guest

  5. #5

    Default Re: query syntax to replace column value from another table?

    On May 4, 6:11 pm, "Paul Lautman" <com> wrote: 

    > > +----+---------------+-------------+--------------+-------------+ 
    > > +----+---------------+-------------+--------------+-------------+
    > > 4 rows in set (0.00 sec)[/ref]

    > > +----+----------+ 
    > > +----+----------+
    > > 4 rows in set (0.00 sec)[/ref]

    >
    > You are not setting a "column" equal to nickname.id, you are joining 2 rows
    > where the id's match.
    >
    > Try this to see what I mean:
    > SELECT
    > *
    > FROM `people`
    > JOIN `nicknames` USING(`id`)[/ref]

    I think that's definitely a step in the right direction. In these
    results:

    select * from people join nicknames using (`id`);
    +----+---------------+-------------+--------------+-------------+----
    +----------+
    | id | name | best_friend | worst_friend | worst_enemy | id |
    nickname |
    +----+---------------+-------------+--------------+-------------+----
    +----------+
    | 1 | James Robert | 2 | 3 | 4 | 1 |
    Jim Bob |
    | 2 | Lawrence | 1 | 3 | 4 | 2 |
    Screech |
    | 3 | Odorless | 2 | 4 | 1 | 3 |
    Stinky |
    | 4 | William Small | 3 | 1 | 2 | 4 |
    Tiny |
    +----+---------------+-------------+--------------+-------------+----
    +----------+

    .... instead of getting the nickname of the names in the "name" column
    in the "people" table, how would you return the "nickname" values from
    "nicknames" of the three columns in the middle, which are returning
    their "id" values from "people"?

    John Guest

  6. #6

    Default Re: query syntax to replace column value from another table?

    John DeStefano wrote: 
    >> You are not setting a "column" equal to nickname.id, you are joining 2 rows
    >> where the id's match.
    >>
    >> Try this to see what I mean:
    >> SELECT
    >> *
    >> FROM `people`
    >> JOIN `nicknames` USING(`id`)[/ref]
    >
    > I think that's definitely a step in the right direction. In these
    > results:
    >
    > select * from people join nicknames using (`id`);
    > +----+---------------+-------------+--------------+-------------+----
    > +----------+
    > | id | name | best_friend | worst_friend | worst_enemy | id |
    > nickname |
    > +----+---------------+-------------+--------------+-------------+----
    > +----------+
    > | 1 | James Robert | 2 | 3 | 4 | 1 |
    > Jim Bob |
    > | 2 | Lawrence | 1 | 3 | 4 | 2 |
    > Screech |
    > | 3 | Odorless | 2 | 4 | 1 | 3 |
    > Stinky |
    > | 4 | William Small | 3 | 1 | 2 | 4 |
    > Tiny |
    > +----+---------------+-------------+--------------+-------------+----
    > +----------+
    >
    > ... instead of getting the nickname of the names in the "name" column
    > in the "people" table, how would you return the "nickname" values from
    > "nicknames" of the three columns in the middle, which are returning
    > their "id" values from "people"?
    >[/ref]

    well, you can replace each one of those fields with a select of its own
    such as this:

    select people.id, people.name,nickname, (select nickname from nicknames
    where id =best_friend) as bestfriend from `people` join `nicknames` on
    `people`.`id` = `nicknames`.`id`

    add worst friend and enemy as appropraite after the best friend field.

    --
    lark -- net
    To reply to me directly, delete "despam".
    lark Guest

  7. #7

    Default Re: query syntax to replace column value from another table?

    On May 7, 9:58 am, lark <net> wrote: 

    YES... that works! Thank you very much, lark and Paul!

    John Guest

  8. #8

    Default Re: query syntax to replace column value from another table?

    On 7 May, 15:13, John DeStefano <com> wrote: 


    >
    > YES... that works! Thank you very much, lark and Paul![/ref]

    But you really don't want to use Lark's idea. Practice JOINs by simply
    using multiple JOINs, it's much tidier.

    SELECT
    `p`.`id`,
    `p`.`name`,
    `n1`.`nickname`,
    `n2`.`nickname` `best_friend`,
    `n3`.`nickname` `worst_friend`,
    `n4`.`nickname` `worst_enemy`,
    FROM `people` `p`
    JOIN `nicknames` `n1` on `p`.`id` = `n1`.`id`
    JOIN `nicknames` `n2` on `p`.`best_friend` = `n2`.`id`
    JOIN `nicknames` `n3` on `p`.`worst_friend` = `n3`.`id`
    JOIN `nicknames` `n4` on `p`.`worst_enemy` = `n4`.`id`

    But surely your worst enemy is in fact your best friend!


    Captain Guest

Similar Threads

  1. Replace data in one column out of 14
    By Tonny René Poulsen in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: August 20th, 09:49 PM
  2. find/replace ^p by column break
    By Daniel_Willsey@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 2
    Last Post: November 6th, 03:56 PM
  3. alter table alter column syntax problem
    By Chuck in forum MySQL
    Replies: 4
    Last Post: September 5th, 05:17 PM
  4. syntax error in replace statement
    By middletree in forum ASP
    Replies: 8
    Last Post: October 24th, 05:30 PM
  5. replace text outside html syntax regexp php
    By andreas kirschner in forum PHP Development
    Replies: 1
    Last Post: July 17th, 10:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139