sql使用多连接子选择更新多行 [英] sql update multiple rows with multi join subselect

问题描述

这是我先前提出的问题的更新的第2部分.我正在尝试进行以下更新，但此查询实际上似乎没有执行任何操作.

This is an updated part 2 of a question I asked earlier. I'm trying to make the following update, but this query does not actually seem to do anything.

UPDATE  u
SET     graduation_class_id = gc.graduation_class_id
FROM    [user] u
JOIN    graduation_class gc
ON      u.graduation_class_id = gc.graduation_class_id
JOIN    graduation_term gt
ON      gt.graduation_year_id = gc.graduation_year_id

TABLE SCHEMA
**user
user_id
graduation_class_id

**graduation_class
graduation_class_id
graduation_year_id

**graduation_term
graduation_term_id
graduation_year_id

目标是将匹配的gradient_class_id值获取到用户表中.有人告诉我这行不通，因为除非用户已经有匹配的gradient_class_id，否则找不到匹配项.这是一个问题，因为我正试图在那里找到合适的人！

The goal is to get the matching graduation_class_id value into the user table. I've been told this won't work because the match won't be found unless the user already has the matching graduation_class_id. That is a problem, because I'm trying to actually get the proper one in there!

推荐答案

这个想法从根本上注定要失败.您正在尝试使SQL Server自己将毕业类链接到用户，尽管SQL Server没有有关应将哪个毕业类链接到用户的信息.除非您在某处(在用户中，在gradient_class中，在其他表中)有一些数据将user_id链接到毕业学期，班级或年份(或有人手动告诉SQL哪些数据匹配)，否则SQL不能神奇地做到这一点为您找工作.

This idea is fundamentally doomed to fail. You're trying to get the SQL server to link a graduation class to a user by itself, despite the SQL server not having information on which graduation class should be linked to the user. Unless you have some data somewhere (in user, in graduation_class, in some other table) that links a user_id to the graduation term, class, or year (or someone manually telling SQL which data match up), SQL can't magically do that job for you.

这篇关于sql使用多连接子选择更新多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！