0

The below query not checking update statement always insert only , where i am doing the wrong in the below code. 

ALTER Procedure [dbo].[UpdateData] @JobOrder varchar(50) AS BEGIN --DECLARE @UPDATING INT IF EXISTS(SELECT 1 FROM [ADDLINKSERVER].[DATABASE].[dbo].[TABLE] WHERE JobOrder=@JobOrder ORDER BY JobStatus DESC) BEGIN UPDATE [ADDLINKSERVER].[DATABASE].[dbo].[TABLE] SET RepairComment = (SELECT Top 1 Status_Comment FROM LOCALDB.dbo.LOCALTABLE Where REPAIRNO=@JobOrder Order By TRACKINGID desc), Amount = (SELECT Top 1 LABOR_AMT+PARTS_AMT As Amount FROM LOCALDB.dbo.LOCALTABLE Where REPAIRNO=@JobOrder Order By TRACKINGID desc), JobStatus = (SELECT Top 1 Job_Status FROM LOCALDB.dbo.LOCALTABLE Where REPAIRNO=@JobOrder Order By TRACKINGID desc) WHERE JobOrder=@JobOrder END ELSE BEGIN INSERT INTO [ADDLINKSERVER].[DATABASE].[dbo].[TABLE](JobOrder,RepairComment,JobStatus,Branch,Customer, Contact,Technician,ReceiptDate,Complaint,Amount,Warranty,ServiceType) ( Select Top 1 JT.REPAIRNO,JT.StatusComment,JT.JobStatus, SJ.MAINSC,SJ.CUSTOMER + ' ' + SJ.CUSTOMERLAST AS FullName,SJ.Tel,SJ.ENGINEER,SJ.RECEIPTDATE, SJ.DEFECTDESC,SJ.LABOR_AMT+PARTS_AMT As Amount,SJ.WARRANTY,SJ.SERVICETYPE FROM LOCALDB1 SJ INNER JOIN LOCALDB2 JT ON JT.REPAIRNO=SJ.REPAIRNO WHERE JT.REPAIRNO=SJ.REPAIRNO) END END

trying to update the data to remoteserver from localdb.

Improve this question
10
  • I don't see any problem with that proc - it should behave as you expect. EXCEPT for the ORDER BY in your exists - why is that there? It doesn't make any different the result of your EXISTS clause. What datatype is the column JobOrder? If you run SELECT COUNT(*) FROM Table WHERE JobOrder =x before and after you run the proc (for x), are the rows the same or different? Commented Feb 24, 2017 at 7:50
  • You could also rewrite that to not need to use the IF at all and that might be easier to debug. You basically put add to your where on your insert and update like this: WHERE JT.REPAIRNO=SJ.REPAIRNO AND NOT EXISTS (SELECT 1 FROM [ADDLINKSERVER].[DATABASE].[dbo].[TABLE] WHERE JobOrder=@JobOrder) Commented Feb 24, 2017 at 7:52
  • thats true i just to search the data with orderby it was not there i want to see which one is last update to remote. JobOrder is varchar. i dont get what is run count(before and after)? Commented Feb 24, 2017 at 7:55
  • By count, I mean run a count just to confirm that you really inserted a record. Count the records matching the JobOrder before and after you run your proc just to confirm. Commented Feb 24, 2017 at 7:58
  • 1
    They might be the same data but in your second select you aren't filtering on it. You're taking the first random record from the table which probably doesn't match @JobOrder at all ad your inserting it across. I'm guessing you need to change WHERE JT.REPAIRNO=SJ.REPAIRNO to WHERE JT.REPAIRNO = @JobOrder but I can't be sure because I don't know what your tables look like and I don't know what you're trying to do Commented Feb 24, 2017 at 8:07

3 Answers 3

Reset to default
2

The last line of your proc

WHERE JT.REPAIRNO=SJ.REPAIRNO

Does not select any particular job number. It just picks a random record from the source and inserts it.

Change it to this:

WHERE JT.REPAIRNO=@JobOrder
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

To debug this simply put in a valid @JobOrder value and execute the code Highlighting from the "SELECT *" after the second dash commenting out the line......execute the sql and make sure the results are correct

 BEGIN UPDATE [ADDLINKSERVER].[DATABASE].[dbo].[TABLE] SET RepairComment = lc.Status_Comment, Amount = lc.Amount, JobStatus = lc.Job_Status --SELECT * FROM [ADDLINKSERVER].[DATABASE].[dbo].[TABLE] as t1 INNER JOIN (SELECT top 1 Status_Comment ,LABOR_AMT+PARTS_AMT As Amount ,Job_Status FROM LOCALDB.dbo.LOCALTABLE Where REPAIRNO=@JobOrder Order By TRACKINGID desc) as lc ON t1.JobOrder=lc.REPAIRNO WHERE t1.JobOrder=@JobOrder
Improve this answer

Comments

-1

Rather than going this way you get count of your result and check if count >0 than update else you can insert 

SELECT COUNT(*) INTO l_cnt FROM table IF( l_cnt > 0 ) THEN Update END IF;
Improve this answer

2 Comments

count has to count all the rows. EXISTS does not. EXISTS is better
... and thats Oracle syntax!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.