SQL Insert multiple rows on duplicate key update incorrect syntax

Hi there

Hopefully an easy on for you SQL gurus out there.  I want an SQL statement that will insert if a unique key does not exist, and update the record if it does for multiple records.   I have attached my SQL.   On the final line Ive just set enq_stats=3 while testing, but in the final version, I want to UPDATE with the results from the select query.

Just running the insert without the ON DUPLICATE KEY part works perfectly in an empty table, and inserts 10 or so records from the result of the select statement.  As I have set consult_id to be a unique key, this obviously needs the ON DUPLICATE KEY part to then update.  But thats where Im stuck

Hope this makes sense, thanks very much
Mark
insert into leader_stats (consult_id,enq_stats)
select consultants.id,
count(enquiries.id) as enq
from consultants
left join enquiries on consultants.id=consult_id
where enquiries.date_sent between DATEADD(day, -100, CURRENT_TIMESTAMP) and 
DATEADD(day, -1, CURRENT_TIMESTAMP)
group by consultants.id
ON DUPLICATE KEY UPDATE enq_stats=3

Open in new window

LVL 1
marklyeAsked:
Who is Participating?
 
marklyeAuthor Commented:
Hi there

The top query didnt work either.  Thanks though.  For info, Ive found a better way to do it I think by using NOT EXISTS, then running the UPDATE

Thanks for the UPDATE command in the first place though

Cheers
Mark


INSERT INTO leader_stats
(consult_id)
SELECT consultants.id
FROM consultants
WHERE 
not exists (select consult_id from leader_stats
where leader_stats.consult_id = consultants.id)

Open in new window

0
 
SharathData EngineerCommented:
replace your statement with MERGE statement.
MERGE Leader_Stats AS T
      USING (SELECT consultants.id AS Consultants_ID,
                    COUNT(enquiries.id) AS Enq
               FROM consultants
               LEFT JOIN enquiries ON consultants.id=consult_id
              WHERE enquiries.date_sent BETWEEN DATEADD(DAY, -100, CURRENT_TIMESTAMP) 
                AND DATEADD(DAY, -1, CURRENT_TIMESTAMP)
              GROUP BY consultants.id) AS S(Consultants_ID,Enq)
         ON (T.Consult_ID = S.Consult_ID)
       WHEN MATCHED THEN UPDATE SET Enq_Status = S.Enq
       WHEN NOT MATCHED THEN INSERT(consult_id,enq_stats) VALUES (S.Consultant_ID,S.Enq)

Open in new window

0
 
marklyeAuthor Commented:
Hi there

Thanks for ther reply, although it errors with:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.

Im probably doing something stupid!  Im using SQL Express 2005 if that helps...

Cheers
Mark
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
SharathData EngineerCommented:
MERGE statement is new in SQL Server 2008. So you can use the UPDATE and INSERT statement.
------ To update the exisiting records
UPDATE T2
   SET consult_id = T1.consult_id, enq_stats = T1.enq
  FROM (SELECT consultants.id, COUNT(enquiries.id) AS enq
          FROM consultants
          LEFT JOIN enquiries 
            ON consultants.id=consult_id
         WHERE enquiries.date_sent BETWEEN DATEADD(day, -100, CURRENT_TIMESTAMP) and 
               DATEADD(day, -1, CURRENT_TIMESTAMP)
         GROUP BY consultants.id) T1
  JOIN leader_stats T2 ON T1.consult_id = T2.consult_id
 
------ To insert as a new record if not exisit
INSERT INTO leader_stats(consult_id,enq_stats)
       (SELECT consultants.id, COUNT(enquiries.id) AS enq
          FROM consultants
          LEFT JOIN enquiries 
            ON consultants.id=consult_id
         WHERE enquiries.date_sent BETWEEN DATEADD(day, -100, CURRENT_TIMESTAMP) and 
               DATEADD(day, -1, CURRENT_TIMESTAMP)
         GROUP BY consultants.id) T1
  LEFT OUTER JOIN leader_stats T2 ON T1.consult_id = T2.consult_id
 WHERE T2.consult_id IS NULL

Open in new window

0
 
marklyeAuthor Commented:
thanks again for this.  The update works like a dream, but the insert errors with:

Incorrect syntax near 'T1'.

Furthermore, is there a way to work both statements in to the one statement, maybe using ON DUPLICATE KEY UPDATE ?   Although this may not be compatible for SQL express 2005?

Thanks a million...getting there

Mark
0
 
SharathData EngineerCommented:
check the queries
-- Check this one
 INSERT INTO leader_stats(consult_id,enq_stats)
       SELECT consultants.id, COUNT(enquiries.id) AS enq
          FROM consultants
          LEFT JOIN enquiries 
            ON consultants.id=consult_id
         WHERE enquiries.date_sent BETWEEN DATEADD(day, -100, CURRENT_TIMESTAMP) and 
               DATEADD(day, -1, CURRENT_TIMESTAMP)
         GROUP BY consultants.id 
  LEFT OUTER JOIN leader_stats T2 ON consultants.consult_id = T2.consult_id
 WHERE T2.consult_id IS NULL
 
-- If the above query doesn't work, then create a temp table 
SELECT consultants.id AS consult_id , COUNT(enquiries.id) AS enq
  INTO #TempTable
  FROM consultants
  LEFT JOIN enquiries 
    ON consultants.id=consult_id
 WHERE enquiries.date_sent BETWEEN DATEADD(day, -100, CURRENT_TIMESTAMP) and 
       DATEADD(day, -1, CURRENT_TIMESTAMP)
 GROUP BY consultants.id
         
-- use the temp table in your INSERT and UPDATE statements
INSERT INTO leader_stats(consult_id,enq_stats)
SELECT consult_id,enq
 FROM #TempTable T1
 LEFT JOIN leader_stats T2 ON T1.consult_id = T2.consult_id
WHERE T2.consult_id IS NULL
 
UPDATE T2
   SET consult_id = T1.consult_id, enq_stats = T1.enq
  FROM #TempTable T1
  JOIN leader_stats T2 ON T1.consult_id = T2.consult_id

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.