Re: Efficiency; advanced/future SQL constructs

AFAIK, many vendors would soon support upsert (they call it merge also) in SQL. For the other problem OLAP extensions to SQL do it. Thanks
Aakash

"Harlan Messinger" <zzzhmessinger_at_erols.com> wrote in message news:9lu8mf$3r6$1_at_bob.news.rcn.net...
> There are some operations that are so obvious and frequent that I'm amazed
> that they didn't show up in SQL, and that I haven't read anything about
 any
> planned updates to the standards that might incorporate them. Actually,
> before I ask anything else, let me ask this--is an update in the works? Or
> is ANSI SQL now frozen for all time? Meanwhile, in the absence of a direct
> construct for these operations, I sometimes wonder what the most efficient
> way of implementing them is, whether in terms of running efficiency or
> implementation efficiency. In case it affects the answer, I work mostly
 with
> SQL Server, though sometimes Oracle comes up.
>
> One was mentioned here recently--having a batch of data to add to a table
> where each new record should be used to update an existing record if there
> is one, or to insert a record otherwise. Why wouldn't that be a standard
> operation? Given that it *isn't* a standard operation, which of the
> following is more efficient:
>
> 1. For each record, do a SELECT to find an existing record,
> and update on success, insert on failure.
>
> 2. Load the new data into a temporary table, do an UPDATE of the form
>
> update T, TEMP set T.x = TEMP.x [, ...] where T.id = TEMP.id
> [or the same thing with an INNER JOIN if supported]
>
> and then do an INSERT of the form
>
> insert into T
> select id, x [, ...] from TEMP
> where not exists (select 'x' from T where T.id = TEMP.id)
>
> (I assume this last is more efficient than
>
> where TEMP.id not in (select id from T)
>
> Correct?)
>
> Here's an example of another routine type of operation. I can write a
 query
> to get the highest annual salary of any employee in each of my company's
> divisions:
>
> select max(salary) from Employee group by divisionID
>
> But how do I *identify* the highest-paid employees?
>
> select employeeID, name, max(salary) from Employee group by divisionID
>
> obviously doesn't do it, but it would be nice to have something that
 simple.
> Even more complex though possible not quite as general would be a query to
> return all the records from the Sales table connected with the
 highest-paid
> employee in each division.
>
> What IS the best way, at this time, to get this information? IS there a
> single SQL statement that does it, in either ANSI SQL or a specific
 dialect?
>
> I'm curious what similar standard operation types others can think of that
> would be nicely handled by a standard mechanism as well.
>
>
Received on Wed Aug 22 2001 - 20:42:12 CEST