MySQL语句帮助
MySQL语句帮助
提问于 2010-08-05 06:41:07
我用MySQL编写了以下SQL语句：
USE my_database;
SELECT * FROM some_table WHERE some_column IN (1, 2, 3);这将返回一组具有列值的行，该列值是另一个表(称为some_other_table)的行中的键。
a b c d <--this is the column with the key
1
2
3我想说的是，在另一个表中查找值为1的所有行，并执行一些操作(将某些列设为空)
任何帮助都是非常感谢的。
回答 1
Stack Overflow用户
回答已采纳
发布于 2010-08-05 06:48:37
可以，您可以使用多表UPDATE语法：
UPDATE some_other_table
JOIN some_table ON (some_table.some_key = some_other_table.id)
SET some_other_table.some_field = NULL
WHERE some_table.some_column IN (1, 2, 3);示例：
CREATE TABLE some_table (id int, some_column int, some_key int);
CREATE TABLE some_other_table (id int, some_field int);
INSERT INTO some_table VALUES (1, 1, 1);
INSERT INTO some_table VALUES (2, 2, 2);
INSERT INTO some_table VALUES (3, 3, 3);
INSERT INTO some_table VALUES (4, 4, 4);
INSERT INTO some_table VALUES (5, 5, 5);
INSERT INTO some_other_table VALUES (1, 10);
INSERT INTO some_other_table VALUES (2, 20);
INSERT INTO some_other_table VALUES (3, 30);
INSERT INTO some_other_table VALUES (4, 40);之前：
SELECT * FROM some_table;
+------+-------------+----------+
| id | some_column | some_key |
+------+-------------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+------+-------------+----------+
5 rows in set (0.00 sec)
SELECT * FROM some_other_table;
+------+------------+
| id | some_field |
+------+------------+
| 1 | 10 |
| 2 | 20 |
| 3 | 30 |
| 4 | 40 |
+------+------------+
4 rows in set (0.00 sec)之后：
SELECT * FROM some_table;
+------+-------------+----------+
| id | some_column | some_key |
+------+-------------+----------+
| 1 | 1 | 1 |
| 2 | 2 | 2 |
| 3 | 3 | 3 |
| 4 | 4 | 4 |
| 5 | 5 | 5 |
+------+-------------+----------+
5 rows in set (0.00 sec)
SELECT * FROM some_other_table;
+------+------------+
| id | some_field |
+------+------------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | 40 |
+------+------------+
4 rows in set (0.00 sec)更新：对下面的评论进行进一步说明。
另一个例子：
CREATE TABLE amir_effective_reference (class int, inst int, rln int, rclass int, rinst int, chg int, typ int);
CREATE TABLE amir_effective_change (chg int, txn int, rltn int, entry int, effective int);
INSERT INTO amir_effective_reference VALUES (1, 100, 1, 50, 20, 10, 5000);
INSERT INTO amir_effective_change VALUES (10, 100, 100, 500, 200);结果：
UPDATE amir_effective_change
JOIN amir_effective_reference ON (amir_effective_reference.chg = amir_effective_change.chg)
SET amir_effective_change.effective = NULL
WHERE amir_effective_change.rltn IN (100);
SELECT * FROM amir_effective_change;
+------+------+------+-------+-----------+
| chg | txn | rltn | entry | effective |
+------+------+------+-------+-----------+
| 10 | 100 | 100 | 500 | NULL |
+------+------+------+-------+-----------+
1 row in set (0.00 sec)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/3410444
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号