CODEWITHSUNDEEP
sqlsql-servert-sqlsql-updateself-reference
Baris Sari
Baris Sari

Reputation: 193

How can I update a table by using subquery

I have got table which is "Stocks". There are thousands of records inside of this table and I need to UPDATE it acc. to following Example. (I use SQL Server 2008)

STOCKS.stock_name     STOCKS.a_id
A.001                  0
B.001                  0
C.001                 20
A.002                  0
B.002                 10
A.003                  0

After executing the code, I want to have a table which is like:

STOCKS.stock_name     STOCKS.a_id
A.001                 20
B.001                 20
C.001                 20
A.002                 10
B.002                 10
A.003                  0

What needs to happen here is that the SQL understands 001 - 002 and 003 are 3 different groups. (I'm thinking about using SUBSTRING(stock_name,3,20) since the first 2 part of my text value will not be like "AA.002". Grouping will start from the third part of each stock name.) It selects the maximum a.id value and update the rows accordingly. I really need your help about this issue.

Many thanks in advance!

Upvotes: 0

Views: 13792

Answers (5)

Eric
Eric

Reputation: 3257

If you want to update all rows, the query below should work.

UPDATE s
SET s.a_id = b.max_value
FROM Stocks s
JOIN (
    SELECT SUBSTRING(stock_name, 3, 20) AS 'stock_name', MAX(a_id) 'max_value'
    FROM Stocks s
    GROUP BY  SUBSTRING(s.stock_name, 3, 20)
) b on b.stock_name = SUBSTRING(s.stock_name, 3, 20)

Upvotes: 0

Ken Palmer
Ken Palmer

Reputation: 2445

Here is one possibility. I used table variables for clarity. You may want to stick some of this into sub-queries. But this should work right away.

-- Your example data for demonstration purposes.
CREATE TABLE #STOCKS (stock_name nvarchar(5), a_id int)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('A.001', 0)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('B.001', 0)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('C.001', 20)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('A.002', 0)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('B.002', 10)
INSERT INTO #STOCKS (stock_name, a_id) VALUES ('A.003', 0)

-- To capture the stock name fragments, and the maximum value for each name fragment.
DECLARE @StockValues TABLE (stock nvarchar(5), stockNameFrag nvarchar(5), stockValue int)
DECLARE @StockMaxValues TABLE (stockNameFrag nvarchar(5), stockMaxValue int)

-- Data captured in this variable gets used in the next subquery.
INSERT INTO @StockValues (stock, stockNameFrag, stockValue)
SELECT  s.stock_name, SUBSTRING(s.stock_name, 3, 20), s.a_id
FROM    #STOCKS s
ORDER BY s.a_id DESC

-- Now extract the maximum value for each name fragment in @StockValues.
INSERT INTO @StockMaxValues
SELECT  sv1.stockNameFrag, MAX(sv2.MaxValue)
FROM    @StockValues sv1
        INNER JOIN (
            SELECT stockNameFrag, MAX(stockValue) AS MaxValue
            FROM    @StockValues
            GROUP BY stockNameFrag
            ) sv2 ON sv1.stockNameFrag = sv2.stockNameFrag AND sv1.stockValue = sv2.MaxValue
GROUP BY sv1.stock, sv1.stockNameFrag

-- Store results you want
DECLARE @Stock TABLE (stock nvarchar(5), stockMaxValue int)
INSERT INTO @Stock (stock, stockMaxValue)
SELECT  s.stock_name, smv.stockMaxValue
FROM    #STOCKS s
        INNER JOIN @StockMaxValues smv ON smv.stockNameFrag = SUBSTRING(s.stock_name, 3, 20)

-- Now update the #STOCKS table with the max value for each stock.
UPDATE  s1
SET     s1.a_id = s2.stockMaxValue
FROM    #STOCKS s1
        INNER JOIN @Stock s2 ON s1.stock_name = s2.stock

SELECT * FROM #STOCKS

-- Cleanup from the demo table.
DROP TABLE #STOCKS

Upvotes: 1

Baris Sari
Baris Sari

Reputation: 193

First of all thank you all for your answers and supports. Ken Palmer's input made this answer possible for which I appreciate him much. Here is the code which works perfectly:

DECLARE @MAXPRIM table (StockNameFrag nvarchar(20), StockValue int)


INSERT INTO @MAXPRIM(StockNameFrag, StockValue)
SELECT SUBSTRING(stock_name,3,20), Max(a_id)
FROM Stocks
Group By SUBSTRING(stock_name,3,20)

UPDATE STOCKS
set a_id = (select StockValue from @MAXPRIM where StockNameFrag =SUBSTRING(stock_name,3,20))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Is this what you want?

update stocks s
    set id = (select max(s2.id) from stocks s2 where substring(s2.stock_name, 3, 20) = substring(s.stockname, 3, 20)
               )
    where s.id = 0;

Upvotes: 0

Eric
Eric

Reputation: 3257

This will update a stock_name

UPDATE s
SET s.a_id = (
        SELECT MAX(a_id) 
        FROM Stocks
        WHERE SUBSTRING(stock_name, 3, 20) = SUBSTRING(s.stock_name, 3, 20)
)
FROM Stocks s
WHERE SUBSTRING(s.stock_name, 3, 20) = '001'

Upvotes: 0

Related Questions