Prev: SQL Server Performance Local vs Remote.
Next: Trying to get a % of responses from a survey in a query
From: Marilyn on 6 May 2010 12:19
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: Uri Dimant on 6 May 2010 12:40
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: Plamen Ratchev on 6 May 2010 12:47
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: Marilyn on 6 May 2010 13:43
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 13:46
Hi,

Thanks for your response. I only need to update the row with "Alfisols" as
value in TAXOR field. The SELECT statement counts the most dominant soil. I
need to insert that value into TAXOR field having 'Alfisols' as its value.

Marilyn

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