基于使用 SQL 更改单个记录对多个记录进行更改 [英] Making changes to multiple records based on change of single record with SQL

问题描述

我有一张食物表.它们有一个Position"字段，表示它们在列表中出现的顺序(listID 是它们所在的列表，我们不想对另一个列表中的项目重新排序).

I have a table of food items. They have a "Position" field that represents the order they should appear in on a list (listID is the list they are on, we don't want to re-order items on another list).

+--id--+--listID--+---name---+--position--+
|   1  |     1    | cheese   |      0     |
|   2  |     1    | chips    |      1     |
|   3  |     1    | bacon    |      2     |
|   4  |     1    | apples   |      3     |
|   5  |     1    | pears    |      4     |
|   6  |     1    | pie      |      5     |
|   7  |     2    | carrots  |      0     |
| 8,9+ |    3,4+  | ...      |     ...    |
+------+----------+----------+------------+

我希望能够说将梨移动到芯片之前"，这涉及将梨的位置设置为位置 1，然后将中间的所有位置增加 1.这样我的结果表看起来像这样...

I want to be able to say "Move Pears to before Chips" which involves setting the position of Pears to position 1, and then incrementing all the positions inbetween by 1. so that my resulting Table look like this...

+--id--+--listID--+---name---+--position--+
|   1  |     1    | cheese   |      0     |
|   2  |     1    | chips    |      2     |
|   3  |     1    | bacon    |      3     |
|   4  |     1    | apples   |      4     |
|   5  |     1    | pears    |      1     |
|   6  |     1    | pie      |      5     |
|   7  |     2    | carrots  |      0     |
| 8,9+ |    3,4+  | ...      |     ...    |
+------+----------+----------+------------+

所以我需要做的就是SELECT name FROM mytable WHERE listID = 1 ORDER BY position，然后我会按照正确的顺序获取所有食物.

So that all I need to do is SELECT name FROM mytable WHERE listID = 1 ORDER BY position and I'll get all my food in the right order.

是否可以通过单个查询来做到这一点?请记住，列表中的记录可能会向上或向下移动，并且该表包含多个列表的记录，因此我们需要隔离 listID.

Is it possible to do this with a single query? Keep in mind that a record might be moving up or down in the list, and that the table contains records for multiple lists, so we need to isolate the listID.

我对 SQL 的了解非常有限，所以现在我知道的唯一方法是 SELECT id, position FROM mytable WHERE listID = 1 AND position BETWEEN 1 AND 5 然后我可以使用 Javascript (node.js) 将位置 5 更改为 1，并将所有其他位置增加 +1.然后更新我刚刚更改的所有记录.

My knowledge of SQL is pretty limited so right now the only way I know of to do this is to SELECT id, position FROM mytable WHERE listID = 1 AND position BETWEEN 1 AND 5 then I can use Javascript (node.js) to change position 5 to 1, and increment all others +1. Then UPDATE all the records I just changed.

只是每当我尝试阅读 SQL 的内容时，每个人都一直说要避免多次查询并避免进行同步编码之类的事情.

It's just that anytime I try to read up on SQL stuff everyone keeps saying to avoid multiple queries and avoid doing syncronous coding and stuff like that.

谢谢

推荐答案

我不认为这可以在少于两次的查询中方便地完成，这没关系，应该尽可能少的查询，但不能不惜一切代价.这两个查询会像(基于您自己编写的内容)

I do not think this can be conveniently done in less than two queries, which is OK, there should be as few queries as possible, but not at any cost. The two queries would be like (based on what you write yourself)

UPDATE mytable SET position = 1 WHERE listID = 1 AND name = 'pears';
UPDATE mytable SET position = position + 1 WHERE listID = 1 AND position BETWEEN 2 AND 4;

这篇关于基于使用 SQL 更改单个记录对多个记录进行更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！