Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
373 views
in Technique[技术] by (71.8m points)

sql server - sql update multiple rows with multi join subselect

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

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...