sql server (alternate): I have an UPDATE statement that uses the Transact SQL extension of the
UPDATE-statement. I want to replace this with an equivalent ANSI
standard query, but I fail to see how to do this. Here's the query:

UPDATE rx
SET IsGxwApplic =
CASE
WHEN
(SELECT COUNT(*)
FROM BrRol AS br
WHERE br.BrId = br#.BrId) = 1
THEN 'y'
ELSE 'n'
END
FROM #BrRol AS br#
INNER JOIN Br AS b
ON b.BrId = br#.BrId
AND b.BrType = 'Virtual'
INNER JOIN RolX AS rx
ON rx.RolId = br#.RolId

Some background on the tables used in this query:
* RolX is a specialization of another table (Rol - not used in this
query). Both Rol and RolX have the column RolId as primary key.
* Br has BrId as primary key. BrType is another column in this same
table, holding the type of Br.
* The table BrRol holds the many-to-many relationship between Br and
Rol. The primary key consists therefor of the columns BrId and RolId.
* The table #BrRol is a temporary table holding only the rows from
BrRol that have been changed since the IsGxwApplic column was last
recalculated. Rows in #BrRol can either be additions to or removals
from the actual BrRol table. This table might therefor even hold
references to Br's and Rol's that are already deleted from their
respective tables.

The specification for the update is:
"For each row in #BrRol that refers to a (still existing) Br of type
'Virtual' and to a (still existing) Rol that is at present in the RolX
table, the IsGxwApplic attribute of has to be recalculated. The new
value for IsGxwApplic is 'y' if the Br under inspection is related to
exactly one Rol, or 'n' of it is related to 0 or to 2 or more Rol's."

The above may sound ambiguous, as it is theoretically possible that
one Rol appears twice in #BrRol with different BR's, one of which is
related to exactly one Rol while the other relates to two or more. The
design of the database prevents this from happening. Checks in the
triggers ensure that a Rol can only be related to two or more Br of
type 'Virtual' if they are either all related to just that one Rol or
all related to at least one more Rol.


I would gladly give more background to make the query easier to
understand, but I'm not at liberty to discuss specifics of the
application I'm working on. In fact, I even have changed some of the
table and column names to meaningless (I hope) abbreviations and
codes. I hope the information provided is still sufficient to assist
me in converting this query to ANSI.

Thanks in advance!!

Best, Hugo
--

[quoted text, click to view]
standard query, but I fail to see how to do this. <<

Here is a shot at it, based on absolutely no DDL and a vague narrative
description. Well, first thing to get to ANSI/ISO is to replace #
prefixes with something else ("_nbr"? ) and the leading _ has to go.
Here is my guess:

UPDATE Rx
SET isgxwapplic -- storing flags???
= CASE WHEN
EXISTS
(SELECT *
FROM BRrol, #brrol AS BR
WHERE BR.br_id = BRrol.br_id
AND BR.brtype = 'virtual'
AND Rx.rol_id = BRrol.rol_id)
THEN 'y' ELSE 'n' END;

An EXISTS() is usually much faster than counting rows.

[quoted text, click to view]
query). Both Rol and RolX have the column RolId as primary key. <<

You mean a VIEW; surely you did not put the same data in TWO tables!!

[quoted text, click to view]
IsGxwApplic column was last recalculated. <<

This is how we used to do it when we had to use punch cards and magnetic
tape systems. Your RDBMS should not store computed columns. This whole
thing should be in a VIEW that never needs to be updated.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

On 12 Mar 2004 20:17:59 GMT, Joe Celko wielded his Keyboard of Typing
to write:

[quoted text, click to view]

You've got a point there. The DDL is at the end of this message.

[quoted text, click to view]

In SQL Server, all temporary tables need to start with the hash
symbol. AFAIC, this may or may not be different from ANSI, but I need
it this way to ensure that the temporary table is stored in tempdb,
not in the actual database.

I can't find a leading _; maybe yoou meant a leading #?

[quoted text, click to view]

Not good at all. It will update all rows in the RolX table instead of
affecting only the columns that can be matched against #BrRol. I just
reread my original post and now see that I forgot to add that it is
very important that other rows in RolX stay unchanged. That's not a
performance issue but a design demand.

[quoted text, click to view]

It is if you only want to check for existance. I need to check for the
existance of exactly one row that matches the criteria, no less but no
more either.

[quoted text, click to view]

If I meant a view, I would have written it. It's a specialization (aka
a subtype, depending on your favorite design method), holding data
that applies to only a small subset of the rows in the main table. Not
the same data, of course, but extra data. If you have data on several
million persons and you need to store 10 extra columns for only the
persons that are male, white, living in Wyoming and aged between 40
and 45, you could add 10 nullable columns to the table for persons,
but I would add a new table WhiteWyomingMiddleAgedMale and add the new
columns into that table.

[quoted text, click to view]

As a rule of thumb, yes. As an ever-true-beyond-any-doubt gospel, no.

If the calculation is complex, often read and changes are rare,
storing a computed column is often the better choice.

If the calculation is complex or needs lots of data and read access to
the data is time-critical, computed columns should be stored (how long
do you think the queue before the cash dispenser would grow if your
bank decided not to store the current balance, since it can be
computed from the transaction history)

If the calculation uses data that is not stored, a computed column is
the only choice. (E.g. a timestamp or the userid of the user
performing an update)

If it is necessary that the computed column is only updated at
specific intervals and remeins untouched in between, even if the
underlying data changes, it has to be stored.

If changes in the computed column have to fire a trigger, the computed
column has to be stored. (AFAIK, it is not possible to fire a trigger
on changes in a view. At least not in MS SQL Server 2000)

[quoted text, click to view]

If I did that, it would cause the value of IsGxwApplic to change
before it should and it would mean that some important reports that
are made when the UPDATE trigger for RolX fires would not be made
anymore.

I'd be jobless before I could say "Social security". <g>

[quoted text, click to view]

As promised: here's the relevant part of the DDL

CREATE TABLE Rol (RolId INT NOT NULL PRIMARY KEY,
several other columns)
CREATE TABLE RolX (RolId INT NOT NULL PRIMARY KEY,
IsGxwApplic CHAR(1) NULL,
several other columns,
FOREIGN KEY (RolId) REFERENCES Rol(RolId))
CREATE TABLE Br (BrId INT NOT NULL PRIMARY KEY,
BrType CHAR(10) NOT NULL,
several other columns)
CREATE TABLE BrRol (BrId INT NOT NULL,
RolId INT NOT NULL,
PRIMARY KEY (BrId, RolId)
FOREIGN KEY (BrId) REFERENCES Br(BrId)
FOREIGN KEY (RolId) REFERENCES Rol(RolId))
CREATE TABLE #BrRol (BrId INT NOT NULL,
RolId NOT NULL,
PRIMARY KEY (BrId, RolId))
/* No foreign key, as corresponding Br and Rol
may already have been removed! */


Best, Hugo
--

I'll have a go...

UPDATE #brRol
SET IsGwxApplic = CASE WHEN (
SELECT COUNT(*)
FROM BrRol AS br
WHERE br.BrId = #brRol.BrId
) = 1 THEN 'y' ELSE 'n' END
WHERE EXISTS (
SELECT 1
FROM Br
WHERE Br.BrId = #brRol.BrId
AND Br.BrType = 'Virtual'
)
AND EXISTS (
SELECT 1
FROM RolX
WHERE RolX.RolId = #brRol.RolId
)

Hope this helps,
Gert-Jan


[quoted text, click to view]

--

Gert-Jan I like what you have, but you are doing
an update to the temp table #brRol which will disappear and not to the
base table :)

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

You are right. The alias must have confused me (FWIW, I hate UPDATE
statements where the updated table is an alias).

So here is attempt 2. In this attempt I have removed all the confusing
(and silly) aliases.

UPDATE RolX
SET IsGxwApplic = CASE WHEN (
SELECT COUNT(*)
FROM #BrRol
INNER JOIN Br
ON Br.BrId = #BrRol.BrId
AND Br.BrType = 'Virtual'
INNER JOIN BrRol
ON BrRol.BrId = #BrRol.BrId
WHERE #BrRol.RolId = RolX.RolId
) = 1 THEN 'y' ELSE 'n' END
WHERE EXISTS (
SELECT 1
FROM #BrRol
INNER JOIN Br
ON Br.BrId = #BrRol.BrId
AND Br.BrType = 'Virtual'
WHERE #BrRol.RolID = RolX.RolID
)

Gert-Jan


[quoted text, click to view]

--

[quoted text, click to view]

I hate UPDATE statements that perform on a join. Proper ANSI updates
don't allow this and therefore don't need aliasing. The silly transact
extension (popular by some as some updates can be done with much
shorter statements) makes aliasing possible and even mandatory if the
join happens to be a self-join.

As I said - I hate this Transact extension. I replace it with an ANSI
equivalent syntax when I find it - and go to great lengths if I don't
see an ANSI equivalent immediately.

[quoted text, click to view]

re: aliases - I've worked with a database holding tables with quite
long names and requiring many complicated queries with self-joins and
subqueries. Aliases were necessary with many queries and (due to the
long table names) convenient on all. Now, I've grown into the habit of
always using aliases, even when they are not needed.

Thanks for the query - looks promising.

[quoted text, click to view]

I think this has to be changed to SELECT COUNT(DISTINT RolId), to
cater for the possibility that two different Br's are both related to
the same Rol and no onther Rol's.

[quoted text, click to view]

Do you have a specific reason for using where exists (select 1 ...)
instead of:

WHERE RolX.RolID IN (
SELECT #BrRol.RolID
FROM #BrRol
INNER JOIN Br
ON Br.BrId = #BrRol.BrId
AND Br.BrType = 'Virtual'
)

Thanks again. I'll take your suggestion through some tests and see if
it produces the desired results.

Best, Hugo
--

[quoted text, click to view]
<snip>

No. There are typically three methods that achieve about the same thing
(1. EXISTS (), 2. IN (), 3. INNER JOIN). I usually only consider using
IN when I know the subquery will only return few rows (because of
performance implications).
In this case I never really tried to see what the query was actually
doing (I was only looking at syntax) or how many rows might be selected.

Gert-Jan

--

[quoted text, click to view]

That is not a bad habit at all if you ask me. However, I thought it was
silly to have a table called "BrRol" that is aliased to "br" and then a
few lines further down a table called "Br" that is aliased to "b". IMO
it is a bad practice to use the same alias as a base table. It is
confusing to say the least.

Also, I would never use an alias like br#. I think this is also what Joe
Celko commented on, the pound sign in an alias.

My 5 cents,
Gert-Jan

--

[quoted text, click to view]

My fault. I changed (shortened, mainly) the table names to be able to
post this without violating my disclosure restrictions. Upon changing
the table names, I didn't notice that one table now had the same name
as another table's alias.

Best, Hugo
--

[quoted text, click to view]

Careful inspection and testing of your solution has revealed an error,
not in your translation to ANSI but in the original query. To solve
that error, I had to add some more complexity to the original query. I
was still able to make an ANSI-compliant version, using the line of
thinking shown in your query.

However, I was already unhappy with the complexity of the ANSI version
of this query. Now, I'm unhappy with both the ANSI and the non-ANSI
version - I always assume that if a query is hard for me to
understand, it'll be impossible to decode for someone else who may
have to maintain my code later. This query gave me headaches every
time I tried to check if it really did what I wanted. So, I have now
decided to stop trying to use one update with a case construction; I
now have two seperate queries, one setting rows to 'Y' and one setting
rows to 'N'. Lots easier to understand and probably quicker as well.

Anyhow - thanks for your input!!

BTW: Your name sounds Dutch - are you? (I am)

Best, Hugo
--

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
[quoted text, click to view]

I doubt. Maybe easier to read, but almost guaranteed to run twice as
long as one query. Furthermore, you may have a maintenance problem.
The conditions may change, and the programmer - which might not be
you - changes only one of the statments, so that some rows are not
updated, or some rows are updated twice, and in both cases some have
the wrong value.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Yes I am. My creative e-mail address sort of gives that away :-)

Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo) writes:
[quoted text, click to view]

I fought a bit too many performance issues where one part of the problem
was that a not too slow query for one reason was run more than once.
The query runs for six seconds? That's acceptable. Oops, it is run 10
times, and the total response time is one minute? That's bad.

[quoted text, click to view]

And that is not unlikely. I've seen code that I have written myself,
and then has been changed by other colleagues. The code might have been
changed a lot, but the comments are still there. And in many cases no
longer applicable.




--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

I think this clearly shows what amount of careful inspection I give
to the hearders.

(I actually had to go and find the option to show headers in Agent to
check out what you meant <g>)

Best, Hugo
--

[quoted text, click to view]

Quite probable. But unless a query turns out to be a real bottle-neck
in performance, I always value readability and maintainability high
above performance.

[quoted text, click to view]

Yes, that's possible. On the other hand - if the programmer who has to
adapt the code for the changed conditions isn't me, I'd say there's a
fair chance that (s)he wouldn't understand what's happening in the
query and hence wouldn't be able to change it at all.

A programmer might forget to change the second update if (and only if)
(s)he fails to read the comments. Yes - in spite of the comment-less
query I posted here, my queries tend to be heavily commented.

My decision to split the query into two seperate updates was made when
I went to bed after working on this query and the very next morning I
[quoted text, click to view]
it for several minutes. And that was on a commented version of the
query!

Best, Hugo
--