1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

[mysql] compare 2 columns insert a third 1

Discussion in 'Databases' started by darkvisje, Oct 14, 2014.

  1. #1
    Dear all,
    I've a little problem with a mysql statement. I want to insert a new column, with a record from an other table:

    table1:
    id
    username

    table2:
    name
    userid (new column) -> int, default value 0

    What i want to do is compare table1.username with table2.name and insert table1.id into table2.userid if they match. And if there is no match, the userid table can be empty.

    Its a query I run 1 time to get the tables oke

    Someone that can help me with the statement to accomplish this?
     
    Solved! View solution.
    darkvisje, Oct 14, 2014 IP
  2. #2
    You should be able to use an inner join to accomplish this. Inner join will only return rows where the condition in the join is satisfied.

    Something like this (untested):
    UPDATE table2
    INNER JOIN table1 ON table2.name = table1.username
    SET table2.userid = table1.id;
     
    jestep, Oct 14, 2014 IP
  3. darkvisje

    darkvisje Well-Known Member

    Messages:
    231
    Likes Received:
    4
    Best Answers:
    1
    Trophy Points:
    120
    #3
    That was it! need to use a inner join! thanks m8
     
    darkvisje, Oct 14, 2014 IP