CODEWITHSUNDEEP
sqlsql-server
MOHAMED NEJI
MOHAMED NEJI

Reputation: 29

Update the table's column automatically in SQL server

I have two queries the first one return the code and the last price

SELECT s.art_code AS CODE
    ,max(s.DocD_PUHTB) AS PRIX
    ,max(s.Docd_date) AS DATE
FROM documentd s
INNER JOIN (
    SELECT s2.art_code
        ,MAX(s2.Docd_date) AS MaxDate
    FROM documentd s2
    GROUP BY s2.art_code
    ) md ON s.art_code = md.art_code
    AND s.Docd_date = md.MaxDate
    AND s.doc_num LIKE '%bla%'
WHERE s.docd_date >= '2019-01-01'
GROUP BY s.art_code
ORDER BY s.Art_Code

enter image description here

the second query update another table with the result of the first one

UPDATE art
SET art_pv = (
        CASE 
            WHEN art_code = '000022215163'
                THEN 3.608
            WHEN art_code = '0013123'
                THEN 5.932
            WHEN art_code = '082005'
                THEN 2.929
            WHEN art_code = '10001862'
                THEN 0.663
            END
        )

My question is how the second query (Update query) take the result "code" and "price" from the fisrt query (Select query) dynamically and do that do that automatically each hour ?

Upvotes: 1

Views: 92

Answers (2)

Neville Kuyt
Neville Kuyt

Reputation: 29629

I've followed the link to your rextester, and the columns and data don't quite match. I made a SQLFiddle to test.

I think you mean the following:

update art
set art_pv =  n.prix
from art a, 
(SELECT s.art_code AS art_code
    ,max(s.DocD_PUHTB) AS PRIX
    ,max(s.Docd_date) AS DATE
FROM documentd s
INNER JOIN (
    SELECT s2.art_code as art_code
        ,MAX(s2.Docd_date) AS MaxDate
    FROM documentd s2
    GROUP BY s2.art_code
    ) md ON s.art_code = md.art_code
    AND s.Docd_date = md.MaxDate
    AND s.doc_num LIKE '%bla%'
WHERE s.docd_date >= '2019-01-01'
GROUP BY s.art_code) n
where n.art_code = a.art_code;

To run this query on a schedule, you can create a job in SQL Server Agent.

Upvotes: 1

Amirhossein
Amirhossein

Reputation: 1251

For Question one:

use this query ==>

SELECT s.art_code as CODE, max(s.DocD_PUHTB)as PRIX, max(s.Docd_date) as DATE
FROM documentd s 
Where s.doc_num like '%bla%' and s.docd_date >= '2019-01-01'
GROUP BY s.art_code

For Question two:

Insert into #t1 and use by on :

SELECT s.art_code as CODE, max(s.DocD_PUHTB)as PRIX, max(s.Docd_date) as DATE ,CASE 
        WHEN art_code = '000022215163'
            THEN 3.608
        WHEN art_code = '0013123'
            THEN 5.932
        WHEN art_code = '082005'
            THEN 2.929
        WHEN art_code = '10001862'
            THEN 0.663
        END
    ) as NewCol
Into #t1
FROM documentd s 
Where s.doc_num like '%bla%'
GROUP BY s.art_code

Update documentd 
Set art_pv = #t1.NewCol
From #t1

Upvotes: 0

Related Questions