Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 5 of 5 total |
Problem updating table columns via subselect |
Sun, Dec 8 2013 4:49 PM | Permanent Link |
Barry | This is likely not a bug(?), but it caught me by surprise so I thought I'd post it to see if anyone has some comments.
I have 2 tables, Students and BadStudents that have the same table structure. BadStudents table has 2 rows with students that have low grades. Students table has a list of all students, including the BadStudents. (These tables are for demo purposes only (simplified)). Here are the layouts for the two tables. (Structure is the same). EXECUTE IMMEDIATE 'CREATE TABLE "Students" ( "Student_Id" GUID COLLATE "UNI" DEFAULT Current_GUID() NOT NULL, "FullName" VARCHAR(30) COLLATE "UNI_CI", "Grade" INTEGER, "DaysAbsent" INTEGER, CONSTRAINT "ix_Primary" PRIMARY KEY ("Student_Id") ) EXECUTE IMMEDIATE 'CREATE TABLE "BadStudents" ( "Student_Id" GUID COLLATE "UNI" DEFAULT Current_GUID() NOT NULL, "FullName" VARCHAR(30) COLLATE "UNI_CI", "Grade" INTEGER, "DaysAbsent" INTEGER, CONSTRAINT "ix_Primary" PRIMARY KEY ("Student_Id") ); I want to update Grade, DaysAbsent in the Students table with the corresponding column values from the rows in BadStudents using the primary key Student_Id. The Sudents table has 4 rows and BadStudents table has 2 rows. Here is the SQL statement that has the problem: update students set (grade,daysabsent) = (select grade, daysabsent from BadStudents where student_id=students.student_id) This update statement updates the Students table with the BadStudents Grade and DaysAbsent values as expected, BUT it NULLS out all the other Students Grade, DaysAbsent in the rows that are NOT in BadStudents. Why? I thought the SQL statement would update only those rows whose primary key (Student_Id) matched in both tables. It does this PLUS nulls out the same columns in all of the other Students rows. Ouch! Is this the way it is suppose to work? It looks like Students is doing a left join to BadStudents so any missing rows will be set to NULL. I thought it would do an inner join and update only matching rows in Students. The way to correct it is to use: update students set (grade,daysabsent) = (select grade, daysabsent from BadStudents where student_id=students.student_id) where students.student_id in (select student_id from BadStudents); This is done in EDB 2.13 B2. Here is the script in case anyone wants to try it out. Comments? Barry SCRIPT BEGIN EXECUTE IMMEDIATE 'CREATE TABLE "Students" ( "Student_Id" GUID COLLATE "UNI" DEFAULT Current_GUID() NOT NULL, "FullName" VARCHAR(30) COLLATE "UNI_CI", "Grade" INTEGER, "DaysAbsent" INTEGER, CONSTRAINT "ix_Primary" PRIMARY KEY ("Student_Id") ) VERSION 1.00 READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "Students" VALUES (''{0BA8C74B-6373-4505-B503-00795A5B86C3}'', ''Donaldson, Carl'', 76, 32)'; EXECUTE IMMEDIATE 'INSERT INTO "Students" VALUES (''{42A66EFB-EF9D-4D56-A7DA-766720BE11D9}'', ''Smith, John'', 90, 4)'; EXECUTE IMMEDIATE 'INSERT INTO "Students" VALUES (''{937B2090-D8CC-4086-AD65-04C56CA62D9D}'', ''Adams, Nancy'', 55, 76)'; EXECUTE IMMEDIATE 'INSERT INTO "Students" VALUES (''{C3203BCF-EF71-4445-B919-67ACFFE65339}'', ''Jones, Bert'', 87, 2)'; EXECUTE IMMEDIATE 'CREATE TABLE "BadStudents" ( "Student_Id" GUID COLLATE "UNI" DEFAULT Current_GUID() NOT NULL, "FullName" VARCHAR(30) COLLATE "UNI_CI", "Grade" INTEGER, "DaysAbsent" INTEGER, CONSTRAINT "ix_Primary" PRIMARY KEY ("Student_Id") ) VERSION 1.00 READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "BadStudents" VALUES (''{0BA8C74B-6373-4505-B503-00795A5B86C3}'', ''Donaldson, Carl'', 76, 32)'; EXECUTE IMMEDIATE 'INSERT INTO "BadStudents" VALUES (''{937B2090-D8CC-4086-AD65-04C56CA62D9D}'', ''Adams, Nancy'', 55, 76)'; EXECUTE IMMEDIATE 'CREATE TABLE "StudentsOrig" ( "Student_Id" GUID COLLATE "UNI", "FullName" VARCHAR(30) COLLATE "UNI_CI", "Grade" INTEGER, "DaysAbsent" INTEGER, CONSTRAINT "ix_Primary" PRIMARY KEY ("Student_Id") ) VERSION 1.00 READWRITE UNENCRYPTED INDEX PAGE SIZE 8192 BLOB BLOCK SIZE 1024 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768'; EXECUTE IMMEDIATE 'INSERT INTO "StudentsOrig" VALUES (''{0BA8C74B-6373-4505-B503-00795A5B86C3}'', ''Donaldson, Carl'', 76, 32)'; EXECUTE IMMEDIATE 'INSERT INTO "StudentsOrig" VALUES (''{42A66EFB-EF9D-4D56-A7DA-766720BE11D9}'', ''Smith, John'', 86, 5)'; EXECUTE IMMEDIATE 'INSERT INTO "StudentsOrig" VALUES (''{937B2090-D8CC-4086-AD65-04C56CA62D9D}'', ''Adams, Nancy'', 55, 76)'; EXECUTE IMMEDIATE 'INSERT INTO "StudentsOrig" VALUES (''{C3203BCF-EF71-4445-B919-67ACFFE65339}'', ''Jones, Bert'', 80, 4)'; END |
Mon, Dec 9 2013 4:15 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Barry
You've missed out the WHERE clause for Students. The subselect in the circumstances you're using it will effectively give a NULL for those Students not in BadStudents. update students set (grade,daysabsent) = (select grade, daysabsent from BadStudents where student_id=students.student_id) WHERE Student_Id IN (SELECT Student_Id FROM BadStudents) should do the trick. Roy Lambert [Team Elevate] |
Wed, Jan 29 2014 1:02 PM | Permanent Link |
Ben Sprei CustomEDP | "where student_id in"
what if I need a multiple qualifier like where student_id, student_age in .... this produces an error How do I handle this. "Roy Lambert" <roy@lybster.me.uk> wrote in message news:DC07815A-47CB-4A28-8BC3-AE21DA0729C6@news.elevatesoft.com... > Barry > > > You've missed out the WHERE clause for Students. The subselect in the > circumstances you're using it will effectively give a NULL for those > Students not in BadStudents. > > update students set (grade,daysabsent) = (select grade, daysabsent from > BadStudents where student_id=students.student_id) > WHERE Student_Id IN (SELECT Student_Id FROM BadStudents) > > should do the trick. > > Roy Lambert [Team Elevate] > |
Wed, Jan 29 2014 2:16 PM | Permanent Link |
Adam Brett Orixa Systems | where student_id, student_age in ....
WHERE Student_id IN (SELECT ... ) AND Student_Age IN (SELECT ...) |
Wed, Jan 29 2014 8:20 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ben,
<< "where student_id in" what if I need a multiple qualifier like where student_id, student_age in .... this produces an error >> Use this: WHERE (Student_ID, Student_Age) IN (SELECT xxxx, xxxx FROM xxxx .....) It's called a "row value constructor". Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Sunday, May 5, 2024 at 10:18 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |