oracle SQL中关联更新的问题

发布于 2024-12-03 04:30:50 字数 1103 浏览 1 评论 0原文

我的问题有点太长了：

TL;DR

Oracle 相关更新无法按预期工作，使受害者列没有变化。

表结构

STUDENTS:

STUDENT_ID  NUMBER(5,0)         
LAST_NAME   VARCHAR2(15 BYTE)   
FIRST_NAME  VARCHAR2(15 BYTE)   
MIDDLE_NAME     VARCHAR2(15 BYTE)   
FINANCIAL_AID   NUMBER(7,2) 
CLASS_NO    VARCHAR2(15 BYTE)       
SPECIALITY      VARCHAR2(100 BYTE)

CLASSES:

CLASS_NO    VARCHAR2(15 BYTE)   
SPECIALITY_NO   NUMBER(5,0)

SPECIALITIES:

SPECIALITY_ID   NUMBER(5,0)
SPECIALITY_NAME VARCHAR2(40 BYTE)

Task

基本上，我需要使用 SPECIALITY_NAME 值来扩充 STUDENTS 表的 SPECIALTY 列（现在它填充有 null），我可以通过连接 CLASSES 和 SPECIALTIES 表来获取该值（是的，我知道这很奇怪，并且会破坏数据库规范化，但这就是任务）。

所以这就是我想要做的：

UPDATE STUDENTS S SET SPECIALITY = (SELECT SPECIALITY_NAME FROM 
  (SELECT * FROM STUDENTS NATURAL JOIN
    CLASSES NATURAL JOIN SPECIALITIES) ALLS
  WHERE S.STUDENT_ID = ALLS.STUDENT_ID)

问题

Oracle 说 N 行已更新。，因此查询结果似乎没问题，但 STUDENTS 表中的 SPECIALTY 列仍然只包含空值。

我做错了什么？

原文

My question would be a little too long so:

TL;DR

Oracle correlated update doesn't work as expected, leaving victim column without changes.

Tables structure

STUDENTS:

STUDENT_ID  NUMBER(5,0)         
LAST_NAME   VARCHAR2(15 BYTE)   
FIRST_NAME  VARCHAR2(15 BYTE)   
MIDDLE_NAME     VARCHAR2(15 BYTE)   
FINANCIAL_AID   NUMBER(7,2) 
CLASS_NO    VARCHAR2(15 BYTE)       
SPECIALITY      VARCHAR2(100 BYTE)

CLASSES:

CLASS_NO    VARCHAR2(15 BYTE)   
SPECIALITY_NO   NUMBER(5,0)

SPECIALITIES:

SPECIALITY_ID   NUMBER(5,0)
SPECIALITY_NAME VARCHAR2(40 BYTE)

Task

Basicaly, I need to augment STUDENTS table's column SPECIALITY with SPECIALITY_NAME values (right now it's populated with nulls) which I can get via connecting with CLASSES and SPECIALITIES tables (yep, I know that's weird and will broke database normalization, but that's the task).

So here is what I'm trying to do:

UPDATE STUDENTS S SET SPECIALITY = (SELECT SPECIALITY_NAME FROM 
  (SELECT * FROM STUDENTS NATURAL JOIN
    CLASSES NATURAL JOIN SPECIALITIES) ALLS
  WHERE S.STUDENT_ID = ALLS.STUDENT_ID)

Problem

Oracle says that N rows updated., hence query result seems to be ok, but SPECIALITY column in STUDENTS table still contains only nulls.

What am I doing wrong?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

黑凤梨 2024-12-10 04:30:50

试试这个

update students s set specialty = 
   (select sp.specialty_name from classes c
    join specialities sp
      on sp.speciality_number = c.speciality_number
    where c.class_no = s.class_no)

Try this instead

update students s set specialty = 
   (select sp.specialty_name from classes c
    join specialities sp
      on sp.speciality_number = c.speciality_number
    where c.class_no = s.class_no)

回复收藏 0 原文

~没有更多了~