我想删除两个表的多列数据,可是无法执行
两个表:deviceparameter,deviceparamvalue,使用parmid关联,一对一关系
比如
delete from deviceparameter as a,deviceparamvalue as b where a.paramid=b.paramid and a.deviceid='1001'
无法执行
哪位兄弟帮帮忙解决下
是不是不可以删多多个表的数据
两个表:deviceparameter,deviceparamvalue,使用parmid关联,一对一关系
比如
delete from deviceparameter as a,deviceparamvalue as b where a.paramid=b.paramid and a.deviceid='1001'
无法执行
哪位兄弟帮帮忙解决下
是不是不可以删多多个表的数据
from deviceparamvalue as b
where exists (select 1 from deviceparameter as a
where a.paramid=b.paramid and a.deviceid='1001')delete deviceparameter
from deviceparameter as a
where a.deviceid='1001'
不过有更好的解决方法吗,可以一条语句就可以删除吗?
DELETE FROM deviceparameter USING deviceparamvalue
WHERE paramid = deviceparamvalue.paramid AND deviceid='1001';
好像不可以,先是报错:
ERROR: column reference "paramid" is ambiguous我改了一下:
DELETE FROM deviceparameter USING deviceparamvalue
WHERE deviceparameter.paramid = deviceparamvalue.paramid AND deviceid='1001'然后执行又会报这个错:
update or delete on "deviceparameter" violates foreign key constraint "fk_paramid" on "deviceparamvalue"
DETAIL: Key (paramid)=(37418) is still referenced from table "deviceparamvalue".奇怪哎怎么回事,我的确是用外键关联的阿
DETAIL: Key (paramid)=(37418) is still referenced from table "deviceparamvalue". 你改了后的语句是对的。
这个错误是你外键约束所致。