mysql中关联子查询问题 -

J-mate

浏览: 8590 次
性别:
来自: 上海

最近访客更多访客>>

遗失笑靥如花

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (6)

社区版块

存档分类

mysql中关联子查询问题

MySQL

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);

分享到：