Insert values in table depending on values of another table mysql

Question

Well my problem is like this: I have to work with a database that has the tables 'user' and 'phone': Tha connection between these tables is with the key 'iduser' in table user and 'user' in table phone. In the table phone I have 98 entries and in the table user I have 1240 entries, how I can complete the 1240 with a default value in table phone using the iduser of table user without repeat the entries that exist now?

Is there a way to do that? I have to do it because I would only do a query instead 2 or 3. Thanks and sorry for my bad English.

krokodilko · Accepted Answer · 2015-06-11 18:14:33Z

1

If I undenstard your question correctly, this problem can be explained using the below simplified example:

There are the following records in table users:

| iduser |
|--------|
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |

and there are the following records in table phone only for two users:

| user |       phone |
|------|-------------|
|    2 | 123-343-444 |
|    5 | 222-444-363 |

and you want to insert records with some "default" phone, say 111-222-333 for users which are not in that table (users 1, 3 and 4), and in the end the table should look like this:

| user |       phone |
|------|-------------|
|    1 | 111-222-333 |
|    2 | 123-343-444 |
|    3 | 111-222-333 |
|    4 | 111-222-333 |
|    5 | 222-444-363 |

If yes, then use the following query:

INSERT INTO phone( user, phone )
SELECT iduser, '111-222-333'
FROM user
WHERE iduser NOT IN ( SELECT user FROM phone );

Demo: http://sqlfiddle.com/#!9/94158/2

answered Jun 11, 2015 at 18:14

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DanteLR Over a year ago

Thanks!! It was so useful what I'm doing.

Collectives™ on Stack Overflow

Insert values in table depending on values of another table mysql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related