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

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]

"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]
>

where student_id, student_age in ....

WHERE Student_id IN
 (SELECT ... )
AND Student_Age IN
 (SELECT ...)

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