Add a Foreign Key to an Existing Table that Contains what would be Invalid FK Values (MYSQL) -
i need add foreign key table exists , populated data contain invalid foreign key values. (mysql)
i know there several questions along these lines, can't seem find answer scenario.
table , data structure
"gbltable" contains "org" field needs become fk of org table. org table has pk field called "number".
currently, gbltable contains non-existent org numbers (ie. if org table has rows pks 1,2, , 3, gbltable might have rows org 4 or 5). while case, cannot apply constraint reference gbltable.org org.number.
i believe best approach particular situation will set fk field in rows null before apply constraint. null valid gbltable.org value program, achieve acceptable outcome.
what have far
want set gbltable.org values null not match valid org.number.
in pseudocode:
set gbltable.org null gbltable.number 1 of following: ( select gbltable.numbers gbltable.org not match existing org.number )
in sql, error "you can't specify target table 'gbltable' update in clause":
update gbltable set org=null number in ( select number gbltable org not in (select number org) )
what's best way achieve requirement?
you don't need level of subquery:
update gbltable set org=null org not in (select number org)
Comments
Post a Comment