CODEWITHSUNDEEP
mysqlimport
Tina Martinesz
Tina Martinesz

Reputation: 31

Mysql single column table --> insert in other table

I have two tables: t1 and t2
- t2 has only 1 column named stuff (60.000 entries).
- t1 has 15 columns, including stuff (empty). t1 has about 650.000 entries.

How can I import the data from t2.stuff in t1.stuff when I have nothing to match it against? (I just want to populate empty fields of t1.stuff with data from t2.stuff and don't care about matching ids or anything.)

The best case (i think) would be, that if I run this query about 11 times, all fields of t1.stuff would be populated, because no empty field in t1.stuff is left over.

Here is an example what the tables look like:

t1:
|__a___|_b_|_c_|stuff|...|
|___308|foo|bar|_____|baz|
|___312|foo|bar|_____|baz|
...
|655578|foo|bar|_____|baz|

t2:
|___stuff___|
|some_info_1|
|some_info_2|
...
|some_info_n|

Maybe there are multiple steps required...

UPDATE

Here is the SOLUTION I went with in case someone has a similar problem - All credits go to user nurdglaw for pointing me in the right direction. So here we go:

  1. Add a new column to your table in question populated with autoincrementing numbers (I set alter table t1 auto_increment = 1 and temporary disabled autoincrementing on my primary key, to avoid an error with this code) ALTER TABLE t1 ADD COLUMN new_column INTEGER UNIQUE AUTO_INCREMENT;

  2. Did the same thing for t2. If you don't already have a second table, you can do something like this: CREATE TABLE t2 (id INTEGER PRIMARY KEY AUTO_INCREMENT,t2_data_column VARCHAR(255)); <-- adjust number to your needs

    and import your data with:
    LOAD DATA LOCAL INFILE 'path_on_your_server/data_file.csv'
    INTO TABLE t2
    LINES TERMINATED BY '\r\n' <-- adjust to your linebreak needs
    (t2_data_column)

  3. Now that you have something to match against, you can INNER JOIN t1 with t2 by doing the following: Add the data from t2 to t1
    UPDATE t1 AS s
    JOIN t2 AS t ON t.id=s.new_column
    SET s.stuff=t.t2_data_column; <-- stuff was the column in t1 I wanted to import the data to.

  4. Tidy up the mess
    DROP TABLE t2;
    ALTER TABLE t1 DROP COLUMN new_column;
    Enable autoincrement on your primary key again and set it to the number you need for new rows, if you used one before.

That is it, you're done!

One further note: I decided to adjust my data offline and import the 650.000 entries needed with this method in one go, rather than doing it with only the 60.000 I put in the initial question. But you'll get the idea of doing it with any number of data and match it with whatever you need.

Upvotes: 0

Views: 488

Answers (2)

sqlab
sqlab

Reputation: 6436

INSERT statements create new rows in your table. You need an UPDATE on the already existing rows

An easy way to do that is using an extern scripting language

; here is a rebol example

; assumming you use the mysql library from softinnov

; and a_ is the name of the unique key to a row in t1

db: open mysql://user:pass@mysql

insert db {select * from t1}

t1rows: copy db

insert db {select * from t2}

t2rows: copy db

foreach row t1rows [

insert db [ {update t1 set t1.stuff = ? where t1.a_ = ?} t2rows/1/1 row/1]

either tail? next t2rows [

  t2rows: head t2rows

] [

  t2rows: next t2rows

]

]

sorry, I still have difficulties with the formatting and the variables in your example

Upvotes: 1

user2399866
user2399866

Reputation:

Try this

     INSERT INTO t1 (stuff)
     SELECT DISTINCT stuff FROM t2

I hope it helps

Upvotes: 0

Related Questions