0

I am creating a node.js server using express. I am using mssql and sqlstring to query my SQL server. For one of my functions, I want to update some data within my 'records' column, however in certain cases, it only updates half of the information and in others, it works perfectly. Here is my current setup:

I created a temporary table #current_samples and have added the columns:

record_id int,
is_positive bit, 
time_of_test datetime2(7)

I am trying to create a temporary table with the new information, then update the records table with the temporary table.

Here is my SQL command I use after creating my temporary table:

UPDATE records SET records.time_of_test = c.time_of_test, records.is_positive = c.is_positive FROM records INNER JOIN #current_samples c ON (records.record_id = c.record_id)

I have checked to make sure my temporary table is correctly adding the columns, and it is. My issue is UPDATE sometimes works perfectly, and other times only updates HALF of the information.

Thanks in advance!

William Au
  • 3
  • 1
  • No one but you can determine what, if anything, is going wrong. As a debugging aid, you can use the OUTPUT clause to log / review the rows that were updated. A slightly less precise check is to retrieve the value of @@rowcount immediately after that statement. If the table has an update trigger, you might want to review the logic. And this _temporary table is correctly adding the columns_ doesn't make sense. You generally don't "add columns" to a table after creation. – SMor Jan 27 '21 at 23:33
  • Does this answer your question? [How to pass table value parameters to stored procedure from .net code](https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code) Use a TVP instead of a temp table – Charlieface Jan 27 '21 at 23:36
  • I will try that. Thank you – William Au Jan 27 '21 at 23:37
  • I assume you are aware that a temp table only exists for the duration of the command? So if you try and create a temp table in one command and access it another it won't be there. – Dale K Jan 27 '21 at 23:38
  • I tried what @Charlieface suggested (using MERGE) and after a few alterations, I got it to work. Also Dale K my temporary tables did not delete until disconnect so that was not the issue. I am still not sure why UPDATE only worked sometimes, and other times only HALF of the information was updated, but MERGE does work so thank you all! – William Au Jan 27 '21 at 23:44
  • 1
    By the sound of it, half the rows didn't actually exist? Are you trying to do an "upsert" (insert/update), then you need `MERGE` – Charlieface Jan 27 '21 at 23:46
  • The columns already existed within my table. I just wanted to update time_of_test and is_positive with new values, however sometimes it worked and other times only half of the values updated. If I ran the command twice, the half that didn't update were fixed. I solved the issue with a `MERGE`, however I am still confused why my UPDATE did not work. – William Au Jan 29 '21 at 02:15

0 Answers0