根据另一个表mysql的值在表中插入值 [英] Insert values in table depending on values of another table mysql
问题描述
我的问题是这样的:我必须使用包含表 'user' 和 'phone' 的数据库:这些表之间的连接是通过表user 中的键'iduser' 和table phone 中的'user' 键来实现的.在table phone中我有98个条目，在table user中我有1240个条目，如何使用table user的iduser在table phone中使用默认值完成1240而不重复现在存在的条目?
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?
有没有办法做到这一点?我必须这样做，因为我只会做一个查询而不是 2 或 3.感谢并抱歉我的英语不好.
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.
推荐答案
如果我没有正确理解你的问题，这个问题可以用下面的简化例子来解释:
表users中有如下记录:
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 |
表phone中只有两个用户的记录:
and there are the following records in table phone only for two users:
| user | phone |
|------|-------------|
| 2 | 123-343-444 |
| 5 | 222-444-363 |
并且您想插入带有一些默认"电话的记录，例如 111-222-333 对于不在该表中的用户(用户 1、3 和 4)，最后该表应如下所示:
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 );
演示:http://sqlfiddle.com/#!9/94158/2
这篇关于根据另一个表mysql的值在表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
