SQLTeam.com | Weblogs | Forums

Update column with value from SAME column


#1

Hi

I'm trying to update a column with a value from the SAME column:

CREATE TABLE #tblpupilnctest
(
PupilID int NOT NULL
,SubjectID int NOT NULL
,Level int NOT NULL
,AssessmentID int NOT NULL
);
INSERT INTO #tblpupilnctest
VALUES (1234, 2, 145, 31)
,(5678, 2, 210, 31)
,(9101, 2, 170, 31)
,(1121, 2, 181,31)

I want to update the LEVEL column for rows that have a subjectID of "12" using the value from equivalent rows that have a "subjectID" of "2", joined on the pupilID and assessmentID

hope that makes sense, thank you


#2

Try this

UPDATE A
SET A.Level = B.level
FROM #tblpupilnctest A
INNER JOIN #tblpupilnctest B
ON A.PupilID = b.PupilID
AND A.AssessmentID = B.AssessmentID
AND A.SubjectID = 2
AND B.SubjectID = 12
WHERE A.SubjectID = 2
SELECT
*
FROM #tblpupilnctest


#3

Thanks very much sqlps - forgive if I'm wrong though, but won't that update "subjectID 2" rows with the value from "subjectID 12" rows, as opposed to the other way round?


#4

I'm trying to update a column with a value from the SAME column: <<
You have never had a course in basic data modeling, so we need to correct your DDL (which I am glad you tried to post – so many people just do not bother with even that Netiquette).

The “tbl-” affix is called a tibble and we laugh at it. Read Phil Factor’s humor pieces on this.

A table needs a key by definition. What you posted are called piles; not quite a file but not a table either. A table also models a set, so its name is plural or collective – if you want a lot of pain read the ISO-11179 standards on this.

Since you do not do math on them, identifiers are not numeric. But noobs are used to having pointers instead of SQL data elements, so they use INTEGERs. Also, people without a data modeling course under their belt do not know what a “_code”, “-id”, “_date”, and other attribute properties mean.

Also LEVEL is a reserved word in ANSI/ISO Standard SQL. But that does not matter; It is too vague to be a column name – assessment_level of what? Water? I will guess “assessment_level” and that it is a dimensionless numeric scale on an assessment tool of some kind.

I see you used punch cards. That is the only reason to put a comma at the start of a line (well, maybe Hebrew or Arabic code). We did this when I started programming so we could re-arrange and re-use the cards in the deck. Today, we know that it just messes up eye movement. That is also why camelCase and PascalCase are no longer used. The underscore lets you use the same data element names in all ISO standards.

Here are my fixes; did I guess right?

CREATE TABLE Pupil_NC_Tests
(pupil_id CHAR(5) NOT NULL PRIMARY KEY, -– required!
subject_code CHAR(3) NOT NULL,
assessment_level INTEGER NOT NULL,
assessment_id CHAR(2) NOT NULL,
);

INSERT INTO Pupil_NC_Tests
VALUES
('1234', '002', 145, '31'),
('5678', '002', 210, '31'),
('9101', '002', 170, '31'),
('1121', '002', 181, '31');

I want to update the assessment_level column for rows that have a subject_code of '12' using the value from equivalent rows that have a 'subject_code' of '002', joined on the pupil_id and assessment_id
hope that makes sense <<

Nope, it does not. Nobody has a subject_code of '12' in the sample data. Then everybody has (subject_code = '002') and (assessment_id = '31').

Mind posting the expected output? We can probably do this with a simple UPDATE. But never use the old Sybase UPDATE..FROM.. syntax; it does not work!* Literally, it does not work; it has cardinality errors.* Google it.