Sqlite更新字段（如果包含）-Sqliteupdatefieldifitcontains

7  

Updates are kind of like if-thens themselves, and there is also if-then support of some sort in most SQL implementations. A simple solution might be:

更新有点像if-thens本身,并且在大多数SQL实现中也有某种类型的if-then支持。一个简单的解决方案可能是

update 
  set widget_id = widget_id + "somenumber/"
  where row_id = X
    and widget_id not like "%/somenumber/%"
    and widget_id not like "somenumber/%";

3  

First, get rid of the symbol-separated list. Use another table, with one widget id per row.

首先,摆脱符号分隔列表。使用另一个表,每行一个小部件ID。

CREATE TABLE ThingieWidgets (
  thingie_id INT REFERENCES Thingies,
  widget_id INT REFERENCES Widgets,
  PRIMARY KEY(thingie_id, widget_id)
);

Fill the table with values from the slash-separated list:

使用斜杠分隔列表中的值填充表格:

INSERT INTO ThingieWidgets (thingie_id, widget_id)
  VALUES (1234, 67), (1234, 797), (1234, 124);

Now you can test if Thingie 1234 references Widget 45:

现在你可以测试Thingie 1234是否引用了Widget 45:

SELECT * FROM ThingieWidgets
WHERE thingie_id = 1234 AND widget_id = 45;

You can try to insert and recover if there's a duplicate key error:

如果出现重复键错误,您可以尝试插入并恢复:

INSERT INTO ThingieWidgets (thingie_id, widget_id)
  VALUES (1234, 45);