CODEWITHSUNDEEP
sqlsql-server
Ben B
Ben B

Reputation: 345

SELECT Aggregate and UPDATE - SQL Server

SQL Server beginner here. I am having some trouble on a SELECT/UPDATE statement. I have a table with an order number column and a line number column. The select statement below returns the values I want. I would like to take the result and insert that number into a column on the same table where the order number matches. Am I over thinking this? Or maybe under thinking the complexity? :)

    SELECT
    ORDNUM, COUNT(LINNUM) AS 'CountLines'
    FROM [TableName]
    WHERE STS = '3' AND DUEQTY < ONHAND AND STYPE = 'CU'
    GROUP BY ORDNUM

    UPDATE [TableName]
    SET LNCNT = 'CountLines'
    WHERE ORDNUM = ORDNUM

Thank you in advance. Ben

UPDATE: I used Andomars solution below and it worked great. Thank you all for the assistance.

Upvotes: 3

Views: 12630

Answers (4)

Lloyd Banks
Lloyd Banks

Reputation: 36649

Try this

UPDATE [tablename]
SET LNCNT = abb.CountLines
FROM (SELECT 
    ORDNUM, COUNT(LINNUM) AS 'CountLines' 
    FROM [TableName] 
    WHERE STS = '3' AND DUEQTY < ONHAND AND STYPE = 'CU' 
    GROUP BY ORDNUM) AS abb

Upvotes: 0

Ali Exalter
Ali Exalter

Reputation: 420

If your in MS SQL have you tried DECLARE.

DECLARE @countlines INT 
SET @countlines = SELECT COUNT(LINNUM) FROM [AdvPickTicket] WHERE STS = '3' 
AND DUEQTY < ONHAND AND STYPE = 'CU' GROUP BY ORDNUM
UPDATE [TableName]
SET LNCNT = @countlines
WHERE ORDNUM = ORDNUM

Upvotes: 0

Andomar
Andomar

Reputation: 238086

Using SQL Server syntax:

update  tn
set     LNCNT = SubQuery.CountLines
from    TableName as tn
join    (
        select  ordnum
        ,       count(linnum) as CountLines
        from    TableName
        where   sts = '3'
                and DUEQTY < ONHAND 
                and STYPE = 'CU'
        group by
                ordnum
        ) as SubQuery
on      SubQuery.ordnum = tn.ordnum

Upvotes: 12

Abe Miessler
Abe Miessler

Reputation: 85056

The following should work for SQL Server:

with counts as
(
    SELECT
    ORDNUM, COUNT(LINNUM) AS 'CountLines'
    FROM [TableName]
    WHERE STS = '3' AND DUEQTY < ONHAND AND STYPE = 'CU'
    GROUP BY ORDNUM
)

    UPDATE tn
    SET tn.LNCNT = c.CountLines
    FROM TableName tn
    INNER JOIN counts as c
    ON tn.OrderNum = c.OrderNum

Upvotes: 2

Related Questions