a表
id (key)
deviceid
devicename
brand
b表
id (key)
deviceid(key)联合主键
devicename (默认值0)
brand (默认值0)
请问如何才能做到到b表中deviceid插入数据时(后),查询a.deviceid 如果a.deviceid=b.deviceid则更新b.devicename and b.brand
我的写法是
BEGIN
UPDATE b SET
devicename = new.devicename,
brand = new.brand
WHERE deviceid = (SELECT deviceid FROM a WHERE brand = new.brand and devicename = new.devicename and brand = new.brand );
end
但是这样写的话给b表插入数据后报错,所以请问各位大牛改如何修改才能达到目的?