MySQL - 删除不在 find_in_set 中的位置 [英] MySQL - delete where not in find_in_set
本文介绍了MySQL - 删除不在 find_in_set 中的位置的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试找到正确的语法来删除不在逗号分隔行中的记录.
表 A
<代码>|身份证 |product_id |属性_id ||----|------------|--------------||1 |123 |45 ||2 |123 |46 ||3 |124 |34 ||4 |124 |33 |
表 B
<代码>|代码 |轴 ||------|-------||123 |45,46 ||124 |34 |
我的目标是从表 A 中删除属性 id 不在表 B 轴值中的所有行(在本例中为 id = 4 的行).
我之前尝试做一个SELECT
:
SELECT A.attribute_id, A.product_idFROM tableA as ALEFT JOIN (SELECT * FROM tableB) AS BON FIND_IN_SET(A.attribute_id, B.`axis`)
但没有任何运气.
我该怎么做?
解决方案
您可以尝试以下SELECT
数据:
SELECT A.attribute_id, A.product_idFROM tableA AS A LEFT JOIN tableB AS B ON A.product_id = B.codeWHERE IFNULL(FIND_IN_SET(A.attribute_id, B.Axis), 0) = 0
您可以使用以下内容DELETE
表A上的行(基于SELECT
):
DELETE FROM tableA WHERE id IN (选择 A.idFROM tableA AS A LEFT JOIN tableB AS B ON A.product_id = B.codeWHERE IFNULL(FIND_IN_SET(A.attribute_id, B.Axis), 0) = 0)
<块引用>
演示: http://sqlfiddle.com/#!9/3a3e13/6/0
I'm trying to find the right syntax to delete records that are not in a comma separated row.
table A
| id | product_id | attribute_id |
|----|------------|--------------|
| 1 | 123 | 45 |
| 2 | 123 | 46 |
| 3 | 124 | 34 |
| 4 | 124 | 33 |
table B
| code | Axis |
|------|-------|
| 123 | 45,46 |
| 124 | 34 |
My goal is to remove all rows from table A where the attribute id is not in the table B axis value (in this example the row with id = 4).
I try to do a SELECT
before:
SELECT A.attribute_id, A.product_id
FROM tableA as A
LEFT JOIN (SELECT * FROM tableB) AS B
ON FIND_IN_SET(A.attribute_id, B.`axis`)
But without any luck.
How can I do this?
解决方案
You can try the following to SELECT
the data:
SELECT A.attribute_id, A.product_id
FROM tableA AS A LEFT JOIN tableB AS B ON A.product_id = B.code
WHERE IFNULL(FIND_IN_SET(A.attribute_id, B.Axis), 0) = 0
You can use the following to DELETE
the rows on table A (based on SELECT
):
DELETE FROM tableA WHERE id IN (
SELECT A.id
FROM tableA AS A LEFT JOIN tableB AS B ON A.product_id = B.code
WHERE IFNULL(FIND_IN_SET(A.attribute_id, B.Axis), 0) = 0
)
这篇关于MySQL - 删除不在 find_in_set 中的位置的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文