Say, I have two tables Courses and faculty_courses - each has a primary key course_ID that is varchar(50) not null.
比方说,我有两个表课程和faculty_courses - 每个都有一个主键course_ID,即varchar(50)非null。
I am trying to delete a row from the Courses table - so assume have to delete that row from both tables since they are linked by a relationship.
我试图从Courses表中删除一行 - 所以假设必须从两个表中删除该行,因为它们是通过关系链接的。
I wrote this - doesn't work - says Incorrect syntax near the keyword 'JOIN'
我写了这个 - 不起作用 - 说关键字'JOIN'附近的语法不正确
DELETE FROM Courses JOIN faculty_courses ON Courses.course_ID = faculty_courses.course_ID WHERE faculty_courses.course_ID = 'ITM731'
从课程中删除JOIN faculty_courses ON Courses.course_ID = faculty_courses.course_ID WHERE faculty_courses.course_ID ='ITM731'
Any ideas?
5 个解决方案
#1
You have to issue two statements.
你必须发表两个声明。
DELETE Courses where course_ID = 'ITM731'
DELETE faculty_courses WHERE course_ID = 'ITM731'
Or, as mentioned here, use a delete cascade.
或者,如此处所述,使用删除级联。
#2
What you want can be handled by having ON DELETE CASCADE links between the tables. Provided that your RDBMS allows for this.
您可以通过在表之间使用ON DELETE CASCADE链接来处理您想要的内容。只要您的RDBMS允许这样做。
#3
You can create a FK reference with CASCADE Delete
您可以使用CASCADE Delete创建FK参考
Edit
I recommend that you use the approach from Ocedecio of explicitly deleting from the two tables as opposed to cascading. It makes your intention so much clearer.
我建议您使用Ocedecio的方法明确地从两个表中删除而不是级联。它使你的意图更加清晰。
#4
DELETE only delete record from one table - the JOIN syntax is useful only for selecting the correct rows on that table, for example, on table with an 1:n relationship (not what you have here) and you want to delete all the records from the "n" table that pertain to a selectable record on the "1" table, then you'd do DELETE FROM ntable INNER JOIN reftable ON ntable.ref = reftable.ref WHERE reftable.column = 'whatyouneedtolookup'.
DELETE只从一个表中删除记录 - JOIN语法仅用于在该表上选择正确的行,例如,在具有1:n关系的表上(不是您在此处拥有的)并且您想要删除所有记录“n”表与“1”表上的可选记录有关,那么你可以执行DELETE FROM ntable INNER JOIN reftable ON ntable.ref = reftable.ref WHERE reftable.column ='whatyouneedtolookup'。
Now as I said, this does not pertain to your current situation, in which you simply need to issue two DELETE statements - one for each table, on the same key.
现在正如我所说的,这与您当前的情况无关,在这种情况下,您只需要在同一个键上发出两个DELETE语句 - 每个表一个。
That being said, it sounds like you have a 1:1 reference between the table which strikes me as odd - normally with that kind of reference, the trivial normalization would be to merge the tables into a single table with the columns from both tables.
话虽如此,听起来你在表之间有一个1:1的引用,这对我来说是奇怪的 - 通常用这种引用,琐碎的规范化将把表合并到两个表中的列的单个表中。
#5
alter table tablename drop column columnname
#1
You have to issue two statements.
你必须发表两个声明。
DELETE Courses where course_ID = 'ITM731'
DELETE faculty_courses WHERE course_ID = 'ITM731'
Or, as mentioned here, use a delete cascade.
或者,如此处所述,使用删除级联。
#2
What you want can be handled by having ON DELETE CASCADE links between the tables. Provided that your RDBMS allows for this.
您可以通过在表之间使用ON DELETE CASCADE链接来处理您想要的内容。只要您的RDBMS允许这样做。
#3
You can create a FK reference with CASCADE Delete
您可以使用CASCADE Delete创建FK参考
Edit
I recommend that you use the approach from Ocedecio of explicitly deleting from the two tables as opposed to cascading. It makes your intention so much clearer.
我建议您使用Ocedecio的方法明确地从两个表中删除而不是级联。它使你的意图更加清晰。
#4
DELETE only delete record from one table - the JOIN syntax is useful only for selecting the correct rows on that table, for example, on table with an 1:n relationship (not what you have here) and you want to delete all the records from the "n" table that pertain to a selectable record on the "1" table, then you'd do DELETE FROM ntable INNER JOIN reftable ON ntable.ref = reftable.ref WHERE reftable.column = 'whatyouneedtolookup'.
DELETE只从一个表中删除记录 - JOIN语法仅用于在该表上选择正确的行,例如,在具有1:n关系的表上(不是您在此处拥有的)并且您想要删除所有记录“n”表与“1”表上的可选记录有关,那么你可以执行DELETE FROM ntable INNER JOIN reftable ON ntable.ref = reftable.ref WHERE reftable.column ='whatyouneedtolookup'。
Now as I said, this does not pertain to your current situation, in which you simply need to issue two DELETE statements - one for each table, on the same key.
现在正如我所说的,这与您当前的情况无关,在这种情况下,您只需要在同一个键上发出两个DELETE语句 - 每个表一个。
That being said, it sounds like you have a 1:1 reference between the table which strikes me as odd - normally with that kind of reference, the trivial normalization would be to merge the tables into a single table with the columns from both tables.
话虽如此,听起来你在表之间有一个1:1的引用,这对我来说是奇怪的 - 通常用这种引用,琐碎的规范化将把表合并到两个表中的列的单个表中。
#5
alter table tablename drop column columnname