代码之家 › 专栏 › 技术社区 › Amir Afghani

MySQL语句帮助

mysql sql

Amir Afghani · 技术社区 · 14 年前

我在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 回复 | 直到 14 年前

Daniel Vassallo 14 年前

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)