Update multiple rows with different values based on Ids

Question

I have a table called Todo. This contains a list of id's and a completedDate field which is NULL. There are some other columns but they are not used here.

I then have a query which returns the following data: data

The query is from the same table and is as follows...

SELECT Todo.id, MIN(CloudCall.CloudCallHistory.CallStarted)
FROM Todo 
JOIN CloudCall.CloudCallHistory ON CloudCall.CloudCallHistory.ObjectId = Todo.foreignId
JOIN CloudCall.CloudCallNotebookTypeCategoryLink ON CloudCall.CloudCallNotebookTypeCategoryLink.CategoryCode = CloudCall.CloudCallHistory.CategoryId
JOIN NotebookTypes ON NotebookTypes.NotebookTypeId = CloudCall.CloudCallNotebookTypeCategoryLink.NotebookTypeId
WHERE CloudCall.CloudCallHistory.CallStarted > Todo.foreignDate 
    AND Todo.completedDate IS NULL 
    AND Todo.cancelledDate IS NULL
    AND NotebookTypes.NotebookFolderId = 175
    AND CloudCall.CloudCallHistory.CategoryId != 17427
GROUP BY Todo.id

So what I want to do is update the Todo table with the new date where the id's match. Is there anyway to do this in 1 query?

It would look something like this maybe?:

UPDATE Todo
SET completedDate... (SELECT...)
WHERE id = ?

where the select would be the query that returned the data shown in the image. Thanks

Can't you put a join between Todo in Update query with the table of the inner query? — Manish Surolia, Dec 12 '18 at 12:15

Joakim Danielson · Accepted Answer · 2018-12-12T12:59:47.030

Something like this

UPDATE Todo
SET completedDate = o.otherDate
FROM Todo t
INNER JOIN (SELECT otherDate, id FROM otherTable) AS o ON t.id = o.id

Update

To join against the query that collects the dates:

UPDATE Todo
SET completedDate = o.min_date
FROM Todo t
JOIN (SELECT Todo.id, MIN(CloudCall.CloudCallHistory.CallStarted) AS min_date
      FROM Todo 
      JOIN CloudCall.CloudCallHistory ON CloudCall.CloudCallHistory.ObjectId = Todo.foreignId
      JOIN CloudCall.CloudCallNotebookTypeCategoryLink ON CloudCall.CloudCallNotebookTypeCategoryLink.CategoryCode = CloudCall.CloudCallHistory.CategoryId
      JOIN NotebookTypes ON NotebookTypes.NotebookTypeId = CloudCall.CloudCallNotebookTypeCategoryLink.NotebookTypeId
      WHERE CloudCall.CloudCallHistory.CallStarted > Todo.foreignDate 
        AND Todo.completedDate IS NULL 
        AND Todo.cancelledDate IS NULL
        AND NotebookTypes.NotebookFolderId = 175
        AND CloudCall.CloudCallHistory.CategoryId != 17427
      GROUP BY Todo.id) AS o ON o.id = t.id

They are from the same table. The query is doing joins to other tables though — Will Daniels, Dec 12 '18 at 12:30
@WillDaniels I have updated my answer based on the query in your question — Joakim Danielson, Dec 12 '18 at 13:00
Thanks! this works, you was 1 minute quicker than the other guy so I can accept this answer — Will Daniels, Dec 12 '18 at 13:14

score 1 · Answer 2 · answered Dec 12 '18 at 12:19

1

Just use JOIN for this purposes:

UPDATE t1 
SET t1.completedDate = t2.[Column]
FROM dbo.ToDo AS t1
INNER JOIN dbo.Table2 AS t2
ON t1.CommonField = t2.[Common Field]
WHERE t1.id in (...);

answered Dec 12 '18 at 12:19

StepUp

36,391
15
88
148

Saber CHETIOUI · Answer 3 · 2018-12-12T13:01:53.390

0

Try this:

UPDATE Todo SET completedDate=CloudCall.CloudCallHistory.CallStarted   
FROM Todo 
JOIN CloudCall.CloudCallHistory ON CloudCall.CloudCallHistory.ObjectId = Todo.foreignId
JOIN CloudCall.CloudCallNotebookTypeCategoryLink ON 
CloudCall.CloudCallNotebookTypeCategoryLink.CategoryCode = 
CloudCall.CloudCallHistory.CategoryId 
JOIN NotebookTypes ON    
NotebookTypes.NotebookTypeId = CloudCall.CloudCallNotebookTypeCategoryLink.NotebookTypeId 
WHERE    
CloudCall.CloudCallHistory.CallStarted > Todo.foreignDate     AND 
Todo.completedDate IS NULL     AND Todo.cancelledDate IS NULL    AND 
NotebookTypes.NotebookFolderId = 175    AND 
CloudCall.CloudCallHistory.CategoryId != 17427
   GROUP BY Todo.id

edited Dec 12 '18 at 13:01

answered Dec 12 '18 at 12:19

Saber CHETIOUI

50
1
13

Thanks im going to give this a try, 1 minute – Will Daniels Dec 12 '18 at 12:23
They are from the same table, how would i match the ids? – Will Daniels Dec 12 '18 at 12:30
1

I am a little confused here, you have a table with CompleteDate field which you said it's NULL, an you shared a set of records filled with dates, and then you say it's the same table! – Saber CHETIOUI Dec 12 '18 at 12:35
yes i will update my question with the actual query so u can see – Will Daniels Dec 12 '18 at 12:36
So ive added the query, it does a few joins in order to return data but the primary table is Todo – Will Daniels Dec 12 '18 at 12:38
Thanks! The other guy posted his answer just before but thankyou very much anyway i appriciate it! – Will Daniels Dec 12 '18 at 13:14

Aaron Bertrand · Answer 4 · 2018-12-12T13:04:34.417

Please use aliases, makes the query readable without eight pages of horizontal scrolling.

Because you're dealing with aggregate data, you'll need to use a variation on SQL Server's proprietary UPDATE FROM syntax:

;WITH aggData AS
(
    SELECT Todo.id, MinStart = MIN(ch.CallStarted)
    FROM Todo 
    INNER JOIN CloudCall.CloudCallHistory AS ch
      ON ch.ObjectId = Todo.foreignId
    INNER JOIN CloudCall.CloudCallNotebookTypeCategoryLink AS cl
      ON cl.CategoryCode = ch.CategoryId
    INNER JOIN NotebookTypes AS nt
      ON nt.NotebookTypeId = cl.NotebookTypeId
    WHERE ch.CallStarted > Todo.foreignDate 
        AND Todo.completedDate IS NULL 
        AND Todo.cancelledDate IS NULL
        AND nt.NotebookFolderId = 175
        AND ch.CategoryId <> 17427
    GROUP BY Todo.id
)
UPDATE t SET completedDate = aggData.MinStart
FROM Todo AS t
INNER JOIN aggData 
ON t.id = aggData.id;

You can probably simplify this so that Todo is only read once, but let's start with something that doesn't drastically rewrite the entire query.

Update multiple rows with different values based on Ids

4 Answers4