mysql中关联子查询问题

最新推荐文章于 2022-10-31 21:38:03 发布

J-mate

最新推荐文章于 2022-10-31 21:38:03 发布

阅读量118

点赞数

文章标签： MySQL

本文链接：https://blog.csdn.net/mwj850119/article/details/83880771

版权

1. 需求：两张表FangAn和FangAnDetail，一对多的关系，根据条件更新FangAnDetail的两个字段。

2.表结构：

FangAn表

+----------------+-------------+------+-----+---------------------+----------------+

+----------------+-------------+------+-----+---------------------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| lot | varchar(30) | NO | | | |

| playType | int(10) | NO | | 0 | |

| money | int(10) | NO | | 0 | |

| lastUpdateDate | timestamp | NO | | 0000-00-00 00:00:00 | |

| status | int(2) | NO | | 0 | |

| catId | bigint(20) | NO | MUL | | |

| expertId | bigint(20) | NO | MUL | | |

| startQihaoId | bigint(20) | NO | MUL | | |

| endQihaoId | bigint(20) | NO | MUL | | |

+----------------+-------------+------+-----+---------------------+----------------+

FangAnDetail表

+----------------+------------+------+-----+---------------------+----------------+

+----------------+------------+------+-----+---------------------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| multi | int(2) | NO | | 0 | |

| touMoney | int(10) | YES | | NULL | |

| leiMoney | int(10) | YES | | NULL | |

| zhuanMoney | int(10) | YES | | NULL | |

| lastUpdateDate | timestamp | NO | | 0000-00-00 00:00:00 | |

| status | int(2) | NO | | 0 | |

| qihaoId | bigint(20) | NO | MUL | | |

| fanganId | bigint(20) | NO | MUL | | |

+----------------+------------+------+-----+---------------------+----------------+

UPDATE expertfangandetail detail1 SET detail1.leiMoney=600, detail1.zhuanMoney=400 WHERE detail1.id IN
	(SELECT a.id FROM(SELECT detail2.id FROM expertfangandetail detail2, expertfangan fangan WHERE detail2.fanganid=fangan.id
 		AND fangan.playtype=301 AND detail2.toumoney=600) a);