1. 需求:两张表FangAn和FangAnDetail,一对多的关系,根据条件更新FangAnDetail的两个字段。
2.表结构:
FangAn表
+----------------+-------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key| Default | Extra |
+----------------+-------------+------+-----+---------------------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| lot | varchar(30) | NO | | | |
| playType | int(10) | NO | | 0 | |
| money | int(10) | NO | | 0 | |
| addDate | timestamp | NO | | CURRENT_TIMESTAMP | |
| 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表
+----------------+------------+------+-----+---------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------+------+-----+---------------------+----------------+
| 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 | |
| addDate | timestamp | NO | | CURRENT_TIMESTAMP | |
| 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);