?
Solved

SQL Insert multiple rows on duplicate key update incorrect syntax

Posted on 2008-11-17
7
Medium Priority
?
4,750 Views
Last Modified: 2013-11-16
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

0
Comment
Question by:marklye
  • 3
  • 3
6 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 22982705
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
 
LVL 1

Author Comment

by:marklye
ID: 22989773
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22990038
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 1

Author Comment

by:marklye
ID: 22990287
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
 
LVL 41

Expert Comment

by:Sharath
ID: 22990362
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
 
LVL 1

Accepted Solution

by:
marklye earned 0 total points
ID: 23001289
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What we learned in Webroot's webinar on multi-vector protection.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question