Mysql从一个表中选择多个id并将它们插入到另一个表中

发布于 2024-11-30 08:30:29 字数 989 浏览 0 评论 0原文

我有一个名为 user_id 的 mysql 表

jos_users_quizzes with the following columns:

id
quiz_i
duser_id

I have a second table called  jos_users with this columns
id
name
username
department

，第一个表与第二个表的 id 链接，所以 quiz_id = id (jos_users) 如何构建一个查询以将选定部门的 id 多次插入 jos_users_quizzes 表中...一键单击

我认为子查询或循环就可以了，但不确定如何构建查询。我需要从所选部门中选择所有用户 ID。例如，有一个部门列表，选择该部门后，选择与该部门相关的所有 ID，并将所有 ID 插入到另一个表中（quizid，（alldepartment ids）

提前致谢！

代码来自 ASP.NET 表单以插入....

  string quizidselected = DropDownList1.SelectedValue;
            string deptselected = ListBox2.SelectedValue;
            //OdbcCommand cmd = new OdbcCommand("INSERT INTO jos_jquarks_users_quizzes  (quiz_id,user_id) VALUES (' " + quizidselected + " ',677)");
            OdbcCommand cmd = new OdbcCommand("INSERT INTO jos_jquarks_users_quizzes (user_id, quiz_id)    SELECT id, ' " + quizidselected + " ' FROM jos_users  WHERE department = ' " + deptselected + " '");

原文

I have a mysql table called

jos_users_quizzes with the following columns:

id
quiz_i
duser_id

I have a second table called  jos_users with this columns
id
name
username
department

the user_id on first table is linked with the id of second table so quiz_id = id (jos_users)
How can build a query to multiple insert the ids of a selected department into the jos_users_quizzes table... IN ONE CLICK

I am thinking a sub query or a loop will do , but no sure how to contruct the query.
I need to select all user ids from selected department. For example have a list of departments, and once the department is selected , select all ids pertaining that department and insert all the Ids into the other table (quizid , (alldepartment ids)

Thanks in advance!

Code from and ASP.NET form to insert ....

  string quizidselected = DropDownList1.SelectedValue;
            string deptselected = ListBox2.SelectedValue;
            //OdbcCommand cmd = new OdbcCommand("INSERT INTO jos_jquarks_users_quizzes  (quiz_id,user_id) VALUES (' " + quizidselected + " ',677)");
            OdbcCommand cmd = new OdbcCommand("INSERT INTO jos_jquarks_users_quizzes (user_id, quiz_id)    SELECT id, ' " + quizidselected + " ' FROM jos_users  WHERE department = ' " + deptselected + " '");

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

站稳脚跟 2024-12-07 08:30:29

根据我对你想要的的解释......

INSERT INTO jos_users_quizzes (user_id, quiz_id)
    SELECT id, :new_quiz_id
        FROM jos_users
        WHERE department = :department

Based on my interpretation of what you want...

INSERT INTO jos_users_quizzes (user_id, quiz_id)
    SELECT id, :new_quiz_id
        FROM jos_users
        WHERE department = :department

回复收藏 0 原文

画中仙 2024-12-07 08:30:29

如果您使用自动增量设置 id，那么您可以执行以下操作

insert into jos_users_quizzes (quiz_i) select id from jos_users;

If you set the id using auto increment, then you can do something like this

insert into jos_users_quizzes (quiz_i) select id from jos_users;

回复收藏 0 原文

枫林﹌晚霞¤ 2024-12-07 08:30:29

如果您知道电子邮件地址、部门 ID 或部门名称等关键字，这很容易。

例如：

$depname = "Logistics"; // PHP // department name
$quizid = "Quiz-12"; // PHP // quiz name

然后进行插入查询：

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, '$quizid' FROM `from_table` 
                 WHERE department = '$depname'"; 
?>

为了获得更高的兼容性，如果您从网页获取值，可以使用小写，例如：

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, LOWER('%$quizid%') 
                 FROM `from_table` 
                 WHERE department like LOWER('%$depname%')"; 
?>

从网页插入数据时使用addslashes命令来保护数据库：

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, LOWER('%".addslashes($quizid)."%') 
                 FROM `from_table` 
                 WHERE department like LOWER('%".addslashes($depname)."%')"; 
?>

It is easy if you know keyword like email address, department id or department name.

For example:

$depname = "Logistics"; // PHP // department name
$quizid = "Quiz-12"; // PHP // quiz name

Then make insert query:

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, '$quizid' FROM `from_table` 
                 WHERE department = '$depname'"; 
?>

For more compatibility you can use Lower case if you obtained values from web page, like:

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, LOWER('%$quizid%') 
                 FROM `from_table` 
                 WHERE department like LOWER('%$depname%')"; 
?>

Use addslashes command for protecting database when you insert data from web page:

<?php 
$query = "INSERT INTO `to_table` (user_id, quiz_id) 
                 SELECT id, LOWER('%".addslashes($quizid)."%') 
                 FROM `from_table` 
                 WHERE department like LOWER('%".addslashes($depname)."%')"; 
?>

回复收藏 0 原文

~没有更多了~