CODEWITHSUNDEEP
mysqldatabase
Naguib Ihab
Naguib Ihab

Reputation: 4496

Inserting data into a table from itself and from another table mysql

I have 2 tables:

table1 contains roleid

table2 contains stringtext,table1_roleid (which is a FK to the roleid field in table1)

table2 now contains 5 entries, each with the same table1_roleid value and different stringtext values, I want to replicate the stringtext values in the same table but with a different table1_roleid value that I want to fetch from table1.

To explain further here's a representation:

table1
+--------+
| Roleid |
+--------+
| 1      |
| 2      |
| 3      |
+--------+

table2
+------------+---------------+
| stringtext | table1_roleid |
+------------+---------------+
| text1      |             1 |
| text2      |             1 |
| text3      |             1 |
| text4      |             1 |
| text5      |             1 |
+------------+---------------+

End result for table2 should be:

table2
+------------+---------------+
| stringtext | table1_roleid |
+------------+---------------+
| text1      |             1 |
| text2      |             1 |
| text3      |             1 |
| text4      |             1 |
| text5      |             1 |
| text1      |             2 |
| text2      |             2 |
| text3      |             2 |
| text4      |             2 |
| text5      |             2 |
| text1      |             3 |
| text2      |             3 |
| text3      |             3 |
| text4      |             3 |
| text5      |             3 |
+------------+---------------+

I thought of creating a temporary table copying table2 and each time I can update the table1_roleid in the temp table but I'm after a smarter method where I can put in a loop for example and insert into the same table without having a temporary table.

Upvotes: 1

Views: 598

Answers (2)

Jorge Campos
Jorge Campos

Reputation: 23361

Another way is just create a cartesian plan between the two tables. Is the same as the cross join

Insert into table2 (string_text, table1_roleid)
  select t2.stringtext, t1.Roleid 
    from table1 t1,
         table2 t2
   where t1.Roleid <> 1

See it here: http://sqlfiddle.com/#!2/d7fc3/1

What it do is combine every registry from the specified tables except (or only) those registries that fit the conditions.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269853

You can generate all the rows you want with a cross join. Because the result will already contain some rows in table2, you need to filter them out. Here is one way:

insert into table2(string_text, table1_roleid)
    select t2.string_text, t1.roleid
    from table2 t2 cross join
         table1 t1
    where t1.roleid <> 1;

A more general way would change the where clause to:

    where not exists (select 1
                      from table tt2
                      where tt2.string_text = t2.string_text and
                            tt2.table1_roleid = t1.roleid
                     )

Upvotes: 1

Related Questions