sql query to insert into columns with value from rows

Question

I have the following table as the result of a query say calc_success_rate

server,      service,     timestamp,     success_rate
123.23.23.2  ftp          1:00 am        1
123.23.23.2  http         1:00 am        0.5

I want to insert these values into another table say metrics whose structure is as follows

server       ftpSuccessRate     httpsuccessrate       timestamp  
123.23.23.2  1                  0.5                   1:00

I will be doing a join between calc_success_rate.server and metrics.server and calc_success_rate.timestamp and metrics.timestamp and insert the ftpsuccessrate and httpsuccessrate

Is it possible to do this in one query

I need an update statement something like this

update secondTable st 
set ftpSuccessRate = , httpSuccessRate = 
from firstTable ft 
where ft.server = st.server and ft.timestamp = st.timestamp

Joe Stefanelli · Accepted Answer · 2011-12-13 16:53:54Z

2

INSERT INTO SecondTable
    (server, ftpSuccessRate, httpSuccessRate, timestamp)
    SELECT server, 
           MAX(CASE WHEN service = 'ftp' THEN success_rate ELSE NULL END),
           MAX(CASE WHEN service = 'http' THEN success_rate ELSE NULL END), 
           timestamp
         FROM FirstTable
         GROUP BY server, timestamp;

EDIT: Based on the comment, here's the UPDATE version:

UPDATE st 
    SET ftpSuccessRate = q.ftpSuccessRate,
        httpSuccessRate = q.httpSuccessRate
    FROM (SELECT server,  
                 MAX(CASE WHEN service = 'ftp' THEN success_rate ELSE NULL END) as ftpSuccessRate, 
                 MAX(CASE WHEN service = 'http' THEN success_rate ELSE NULL END) as httpSuccessRate,  
                 timestamp 
              FROM FirstTable 
              GROUP BY server, timestamp) q 
        INNER JOIN @SecondTable st 
            ON q.server = st.server 
                AND q.timestamp = st.timestamp

edited Dec 13, 2011 at 16:53

answered Dec 13, 2011 at 16:37

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user373201 Over a year ago

its more of an update statement on secondTable with join between them using server and the timestamp. update secondTable st set ftpSuccessRate = , httpSuccessRate = from firstTable ft where ft.server = st.server and ft.timestamp = st.timestamp

Joe Stefanelli Over a year ago

@user373201: I'll modify my answer but the subject of your question clearly states "sql query to insert"

MatBailie Over a year ago

@user373201 - The UPDATE assumes that the existence of a "ftp" record in the first table guarantees an "http" record as well. Is that correct? If so, this works great :)

MatBailie Over a year ago

@JoeStefanelli - Still requires an ISNULL or COALESCE so as to avoid replacing existing values with NULL? (Assumes FirstTable never has NULL as a legitimate value though)

Joe Stefanelli Over a year ago

@Dems: Possibly. Those are business rules beyond what was given within the scope of this question.

|

MatBailie · Accepted Answer · 2011-12-13 16:50:55Z

1

INSERT INTO
  youTable
SELECT
  server,
  MAX(CASE WHEN service = 'ftp'  THEN success_rate END)   AS  ftpSuccessRate,
  MAX(CASE WHEN service = 'http' THEN success_rate END)   AS httpSuccessRate,
  timestamp
FROM
  yourOtherTable
GROUP BY
  server,
  timestamp

Or as an update of existing records...

UPDATE
  yourOtherTable
SET
  ftpSuccessRate  = COALESCE(MAX(CASE WHEN service = 'ftp'  THEN success_rate END), yourOtherTable.ftpSuccessRate ),
  httpSuccessRate = COALESCE(MAX(CASE WHEN service = 'http' THEN success_rate END), yourOtherTable.httpSuccessRate)
FROM
  yourOtherTable
LEFT JOIN
  yourtable
    ON  yourTable.server    = yourOtherTable.server
    AND yourTable.timestamp = yourOtherTable.timestamp
GROUP BY
  yourOtherTable.server
  yourOtherTable.timestamp

edited Dec 13, 2011 at 16:50

answered Dec 13, 2011 at 16:38

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Comments

Richard Banks · Accepted Answer · 2011-12-13 16:50:19Z

0

 UPDATE 
  metrics SET 
      ftpSuccessRate = (CASE WHEN service = 'ftp' THEN success_rate ELSE NULL END),
      httpsuccessrate = (CASE WHEN service = 'http' THEN success_rate ELSE NULL END)    
  FROM 
    calc_success_rate 
  WHERE 
    metrics.server = calc_success_rate.Server

answered Dec 13, 2011 at 16:50

Richard Banks

2,9735 gold badges40 silver badges73 bronze badges

Collectives™ on Stack Overflow

sql query to insert into columns with value from rows

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related