question

ewinkiser avatar image
0 Votes"
ewinkiser asked MartinJaffer-MSFT commented

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

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](10) NULL,

Any ideas? Thanks Mike

azure-data-factorysql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@ewinkiser did @EchoLiu-msft 's answer solve your issue? If so please mark as accepted answer, otherwise please provide feedback.

0 Votes 0 ·

@ewinkiser if you found your own solution, could you please share it with the community?

0 Votes 0 ·

1 Answer

EchoLiu-msft avatar image
1 Vote"
EchoLiu-msft answered

Hi @ewinkiser

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.