Announcement

Collapse
No announcement yet.

Setting value as result of another table where X = Y

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Setting value as result of another table where X = Y

    Hi guys,

    I'm looking to write an SQL query that will do the following.

    I have two tables, one with an empty column (just added) called 'member_id' and a column called 'email'.
    The other table has two columns: 'id', 'email'.

    I'm looking to go through each row in the first table (bookings) and populate the 'member_id' value with the 'id' from the second table where the email values match.

    Does this make sense?

    Thanks.

    EDIT:

    I've solved the query, for anyone else wanting to do the same:
    Code:
    UPDATE table1 
    SET table1.id = (
        SELECT table2.id 
        FROM table2
        WHERE table2.email = table1.email
    );
    Last edited by comuloid; 09-09-2015, 08:23 AM.

  • #2
    Code:
       UPDATE T2
       SET MEMBER_ID = T1.ID
       FROM
             Table2 T2
       JOIN
             Table1 T1 ON T2.Email = T1.Email

    This is the best way to do it because you can always replace the UPDATE/SET lines with a simple SELECT * to make sure you're about to update the rows you want to ... and not accidentally updating a bunch of rows you didn't intend to
    Signature Image Rotator will return when I have time to fix it

    Running Scoreboard
    Anthony : 3 points

    Comment


    • #3
      Thanks! I do need to get in the habbit of using join, fortunately this was just a one time query to populate a new column.

      Comment

      Working...
      X