Prev: SQL Server Performance Local vs Remote.
Next: Trying to get a % of responses from a survey in a query
From: Uri Dimant on 6 May 2010 14:04
WITH cte
AS
(
SELECT COUNT(*) OVER (PARTITION BY SNAME ) [SNAME_CNT] ,SNAME FROM
ConusSolLyr
WHERE TAXCL <> ' ' AND TAXOR = 'Alfisols'
GROUP BY SNAME
) UPDATE TOP (1) yourtable SET SNAME=SNAME FROM cte ORDER BY SNAME_CNT
desc


DESC
"Marilyn" <Marilyn(a)discussions.microsoft.com> wrote in message
news:2BC0B5EB-1801-4493-B178-36D962E9F78A(a)microsoft.com...
> Hi,
>
> I'm getting an error: "Incorrect syntax near the keyword 'ORDER'. Thanks
> for replying.
>
> Marilyn
>
> "Uri Dimant" wrote:
>
>> Hi
>> Use common table expression
>> WITH cte
>> AS
>> (
>> SELECT COUNT() OVER (PARTITION BY SNAME ),[SNAME_CNT] ,SNAME FROM
>> ConusSolLyr
>> WHERE TAXCL <> ' ' AND TAXOR = 'Alfisols'
>> GROUP BY SNAME
>> ) UPDATE TOP (1) yourtable SET SNAME=SNAME FROM cte ORDER BY SNAME_CNT
>> DESC
>>
>>
>> "Marilyn" <Marilyn(a)discussions.microsoft.com> wrote in message
>> news:529779BC-7736-4AFB-9CE4-4B9F261EFC14(a)microsoft.com...
>> > Hi,
>> >
>> > Can anyone help me how to Update an existing SQL Server 2008 table
>> > using
>> > the
>> > following select statement:
>> >
>> > SELECT TOP 1 SNAME, COUNT(*)[SNAME_CNT] FROM ConusSolLyr
>> > WHERE TAXCL <> ' ' AND TAXOR = 'Alfisols'
>> > GROUP BY SNAME
>> > ORDER BY SNAME_CNT DESC
>> >
>> > I'd like to update TaxOrder table's SNAME field with the resulting
>> > value
>> > of
>> > SNAME from the above select statement.
>> >
>> > Thanks so much in advance.
>>
>>
>> .
>>


From: Marilyn on 6 May 2010 14:38
Hi,

The following works:

UPDATE TaxOrder
SET SNAME = (SELECT TOP 1 SNAME FROM ConusSolLyr
WHERE TAXCL <> '' AND TAXOR = 'Alfisols'
GROUP BY SNAME
ORDER BY COUNT(*) DESC)
WHERE TAXOR = 'Alfisols'

"Plamen Ratchev" wrote:

> Is there any relation between the tables? Here is one way to perform
> the update but this will update all rows in the target table:
>
> UPDATE TaxOrder
> SET SNAME = (SELECT TOP 1 SNAME
> FROM ConusSolLyr
> WHERE TAXCL <> ' '
> AND TAXOR = 'Alfisols'
> GROUP BY SNAME
> ORDER BY COUNT(*) DESC);
>
> If there is relation you have to add correlation in the subquery based
> on the join columns.
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com
> .
>
From: --CELKO-- on 7 May 2010 08:58
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html
From: Geoff Schaller on 7 May 2010 09:43
You're such an old fart.

The problem was easily dealt with by more tolerant types that appeared
more interested in assisting than asserting some academic dogma from the
past. Is time catching up with you? Did you notice the problem was
solved quite happily without your prognostications?



"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:4171371b-18e7-4a03-92e5-f3486825e10c(a)b18g2000yqb.googlegroups.com:

> "A problem well stated is a problem half solved." -- Charles F.
> Kettering
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect.
>
> Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:
> http://www.catb.org/~esr/faqs/smart-questions.html

From: --CELKO-- on 8 May 2010 00:41
>> You're such an old fart. <<

True, but at least I am not rude :)

>> The problem was easily dealt with by more tolerant types that appeared more interested in assisting than asserting some academic dogma from the past. <<

Actually, look at the thread. Plamen asked "Is there any relation
between the tables?" which is the same thing I did, but I was more
precise about it.

It also says a lot that you regard clear specs as "academic dogma from
the past"! LOL! This is like the joke about programmers who write
code blindly then try to tell the client that it might not be up to
spec but it works as coded.

>> Did you notice the problem was solved quite happily without your prognostications? <<

We don't know if this is solved yet. Marilyn hasn't posted back. From
third posting, my thought was that we need a skeleton like:

UPDATE TaxOrders
SET s_anme
= ( << expression with ConusSolLyr >> )
WHERE taxor = 'Alfisols';

But, we have no idea what the keys are, or even which tables the
columns come from. Her mix of casing and improper data element names
offer no help.

Now, looking at YOUR suggestion .. oh, there wasn't any!
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: SQL Server Performance Local vs Remote.
Next: Trying to get a % of responses from a survey in a query