Search code examples
mysqlsqlinsert-intoselect-into

mysql insert into or select into - copy column from tbl1 into tbl2

So I have 2 MySQL tables in the same database with the following columns (all of which are NOT NULL):

tbl1: (url, urlkey, domain, created, 8 more columns..)

tbl2: (url, urlkey, canonical)

These 2 tables both have 100,000 rows right now - and they correspond to the same url, urlkey values (even inserted in the same order).

What I want to do is: copy the domain column from tbl1 into tbl2 and all the associated domain row values from tbl1. So, after this, tbl2 should look like: (url, urlkey, canonical, domain) and should have the same url, urlkey, domain values in each row as tbl1.

I have tried a few things involving SELECT INTO and INSERT INTO but none have successfully copied the row values for the domain column into tbl2.

Does anyone know how to do this for the particular situation that I am in? Please let me know, thanks everybody

-- Nathan

Solution

  • First you need to add the new column to tbl2

    ALTER TABLE tbl2 ADD domain char(100) //or whatever the type is

    Then you need to populate it like this:

    UPDATE tbl2
JOIN tbl1 ON tbl1.url = tbl2.url AND tbl1.urlkey = tbl2.urlkey
SET tbl2.DOMAIN = tbl1.DOMAIN