Given two tables created and populated as follows:
CREATE TABLE dbo.envelope(id int, user_id int); CREATE TABLE dbo.docs(idnum int, pageseq int, doctext varchar(100)); INSERT INTO dbo.envelope VALUES (1,1), (2,2), (3,3); INSERT INTO dbo.docs(idnum,pageseq) VALUES (1,5), (2,6), (null,0);
What will the result be from the following query:
UPDATE docs SET doctext=pageseq FROM docs INNER JOIN envelope ON envelope.id=docs.idnum WHERE EXISTS ( SELECT 1 FROM dbo.docs WHERE id=envelope.id );
Explain your answer.
The result of the query will be as follows:
idnum pageseq doctext 1 5 5 2 6 6 NULL 0 NULL
EXISTS clause in the above query is a red herring. It will always be true since
ID is not a member of
dbo.docs. As such, it will refer to the
envelope table comparing itself to itself!
idnum value of
NULL will not be set since the join of
NULL will not return a result when attempting a match with any value of