SQL Statement in Stored Proc for ADF Pipeline not Updating...

Question

I am having issues trying to get this to work for some reason: @Nasreen Akter @MartinJaffer-MSFT

Update MercerReportTable  
    SET [CountType10] = (SELECT COUNT(PersonRec10)  
    FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[MercerReportTableMod]  
    WHERE PersonRec10 = 10)  

The statement seems successful when executed, but nothing is updated in the MercerReportTable. There are 3000+ items that should be the result count. The column is the following type, but that should not matter, correct? When I just run

(SELECT COUNT(PersonRec10)
FROM [MercerStagingDev].[MILKY-WAYTEST\AppSQLVST4DotNetDev-R].[MercerReportTableMod]
WHERE PersonRec10 = 10) I get 3624 which is the correct number of rows..but I can't get other table to update....

[CountType10] nchar NULL,

Any ideas? Thanks Mike

Answer 1

Hi @Mike Kiser ，

I did a simple test in sql server. According to my test results, your code seems to be correct:

create  table test1(num int,projectname char(15))  
insert into test1 values(2,'project1'),(2,'project2'),(3,'project2'),  
                        (4,'project3'),(1,'project2'),(2,'project1')  
  
create  table test2(id int,projectname char(15))  
insert into test2 values(1,'project1'),(2,'project1'),(3,'project2'),  
                        (4,'project2'),(5,'project1'),(6,'project3')  
  
select * from test1  
select * from test2  
  
update test2  
set id=(select count(num) from test1 where num=2)  
from test2  
  
drop table test1  
drop table test2  

Could you please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …)
along with your expected result? So that we’ll get a right direction and make some test.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.