如何使用另一个表中的字段更新一个表中的字段？ （SQL）

时间：2022-07-24 23:08:05

Two tables:

COURSE_ROSTER - contains

两个表:COURSE_ROSTER - 包含

  • COURSE_ID as foreign key to COURSES

    • COURSE_ID作为COURSES的外键

  • USER_ID as field I need to insert into COURSES

    • USER_ID作为我需要插入COURSES的字段

COURSES - contains

课程 - 包含

  • COURSE_ID as primary key

    • COURSE_ID为主键

  • INSTRUCTOR_ID as field that needs to be updated with USER_ID field from COURSE_ROSTER

    • INSTRUCTOR_ID作为需要使用COURSE_ROSTER的USER_ID字段更新的字段

What would the UPDATE sql syntax be? I am trying this, but no good... I'm missing something and I can't find it online.

UPDATE sql语法是什么?我正在尝试这个,但没有好...我错过了一些东西,我无法在网上找到它。

UPDATE COURSES 
SET COURSES.INSTRUCTOR_ID = COURSE_ROSTER.USER_ID 
WHERE COURSE_ROSTER.COURSE_ID = COURSES.COURSE_ID

5 个解决方案

#1

Not all database vendors (SQL Server, Oracle, etc.) Implement Update syntax in the same way... You can use a join in SQL Server, but Oracle will not like that. I believe just about all will accept a correclated subquery however

并非所有数据库供应商(SQL Server,Oracle等)都以相同的方式实现更新语法...您可以在SQL Server中使用联接,但Oracle不会那样。我相信几乎所有人都会接受一个相关的子查询

  Update Courses C  
   SET Instructor_ID = 
          (Select User_ID from Course_Roster
           Where CourseID = C.Course_ID)

NOTE: The column User_ID in Course_Roster would probably be better named as InstructorId (or Instructor_Id) to avoid confusion

注意:Course_Roster中的User_ID列可能更好地命名为InstructorId(或Instructor_Id)以避免混淆

#2

    Update Courses
    SET Courses.Instructor_ID = Course_Roster.User_ID
    from Courses Inner Join Course_Roster 
    On Course_Roster.CourseID = Courses.Course_ID

This is assuming that your DBMS allows for joins on your update queries. SQL Server definitely allows this. If you cannot do something like this you need to look towards using a subquery.

这假设您的DBMS允许更新查询的连接。 SQL Server绝对允许这样做。如果你不能做这样的事情,你需要考虑使用子查询。

#3

UPDATE COURSES 
SET COURSES.INSTRUCTOR_ID = COURSE_ROSTER.USER_ID 
FROM COURSES
INNER JOIN COURSE_ROSTER
    ON COURSE_ROSTER.COURSE_ID = COURSES.COURSE_ID

#4

Why do you need the column course.instructor_id if you fill it with COURSE_ROSTER.user_id? Isn't it redundant storage?

如果用COURSE_ROSTER.user_id填充它,为什么还需要列course.instructor_id?它不是冗余存储吗?

#5

UPDATE COURSES 
SET INSTRUCTOR_ID = CR.USER_ID 
FROM COURSES C
INNER JOIN COURSE_ROSTER CR   
   ON CR.COURSE_ID = C.COURSE_ID

#1

Not all database vendors (SQL Server, Oracle, etc.) Implement Update syntax in the same way... You can use a join in SQL Server, but Oracle will not like that. I believe just about all will accept a correclated subquery however

并非所有数据库供应商(SQL Server,Oracle等)都以相同的方式实现更新语法...您可以在SQL Server中使用联接,但Oracle不会那样。我相信几乎所有人都会接受一个相关的子查询

  Update Courses C  
   SET Instructor_ID = 
          (Select User_ID from Course_Roster
           Where CourseID = C.Course_ID)

NOTE: The column User_ID in Course_Roster would probably be better named as InstructorId (or Instructor_Id) to avoid confusion

注意:Course_Roster中的User_ID列可能更好地命名为InstructorId(或Instructor_Id)以避免混淆

#2

    Update Courses
    SET Courses.Instructor_ID = Course_Roster.User_ID
    from Courses Inner Join Course_Roster 
    On Course_Roster.CourseID = Courses.Course_ID

This is assuming that your DBMS allows for joins on your update queries. SQL Server definitely allows this. If you cannot do something like this you need to look towards using a subquery.

这假设您的DBMS允许更新查询的连接。 SQL Server绝对允许这样做。如果你不能做这样的事情,你需要考虑使用子查询。

#3

UPDATE COURSES 
SET COURSES.INSTRUCTOR_ID = COURSE_ROSTER.USER_ID 
FROM COURSES
INNER JOIN COURSE_ROSTER
    ON COURSE_ROSTER.COURSE_ID = COURSES.COURSE_ID

#4

Why do you need the column course.instructor_id if you fill it with COURSE_ROSTER.user_id? Isn't it redundant storage?

如果用COURSE_ROSTER.user_id填充它,为什么还需要列course.instructor_id?它不是冗余存储吗?

#5

UPDATE COURSES 
SET INSTRUCTOR_ID = CR.USER_ID 
FROM COURSES C
INNER JOIN COURSE_ROSTER CR   
   ON CR.COURSE_ID = C.COURSE_ID
标签：

相关文章