Question

mysql 在我插入A表的新数据时，同时插入B表的数据，如果B表有相同字段的数据，则更新B表中的数据

我写个触发器
create trigger updateTotal after insert on _meterhouryield for each row
select

if((select H00 from _totalhouryield where recordDate = 'new.recordDate') = null ,
(insert into _totalhouryield(recordDate, H00_Start, H00, H00_End) values('new.recordDate', new.H00_Start, new.H00, new.H00_End),
(update _totalhouryield SET H00_Start = H00_Start + new.H00_Start, H00 = H00 + new.H00, H00_End = H00_End + new.H00_End WHERE recordDate = 'new.recordDate'));

这个触发器无法创建
错误提示
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'into _totalhouryield(recordDate, H00_Start, H00, H00_End) values('new.recordDate' at line 3
这个是表结构
_meterHourYield表
| id | int(2)
| H00_Start | int(4)
| H00 | int(4)
| H00_End | int(4)
| recordDate | date
| name | varchar(1)

_totalHourYield表
| id | int(2)
| recordDate | date
| H00_Start | int(4)
| H00 | int(4)
| H00_End | int(4)

Answer 1

你可以在盯颤缓插入B表时用 INSERT ... ON DUPLICATE KEY UPDATE 语句。
如果数据重复洞肢 就会执行后面的update

例：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

上面语句 如果a是唯一索引且表里凯模已经有a=1的记录 则上面语句等同于下面：

UPDATE table SET c=c+1 WHERE a=1;