SQL Server Stored Procedure, update / insert

The stored procedure below works, but only updates the first row that meets the where clause.  The second table (corrections) will hold 10-20 records, after the user views the results of the corrections table, they will trigger the stored procedure.  It needs to update all 10-20 records that match with the fieldtagnum key and then delete each record from corrections table.
ALTER-PROCEDURE.docx
LVL 5
rtayIT DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rtayIT DirectorAuthor Commented:
Where do I find said code block button?
Delphineous SilverwingGood Ol' GeekCommented:
It's the link with the word "Code" on the tool bar directly above the box you typed text into - between the Quotes and the </> symbols.
PortletPaulEE Topic AdvisorCommented:
Your query looks like this in a code block:
ALTER PROCEDURE [dbo].[OverwriteCorrections]
 
  AS

  SET NOCOUNT ON

  --update matching rows

  UPDATE

	iea

	SET

	TruckNumber=c.TruckNumber
	
	FROM

	tbl_Corrections c

	
	INNER JOIN

	tbl_ImportExtAG iea

	ON

	c.FieldTagnum = iea.FieldTagnum
	

	--insert non-matching / new rows

    INSERT INTO

	tbl_ImportExtAG(TruckNumber,FieldTagnum)

	SELECT
		c.TruckNumber,c.FieldTagnum

	FROM

		tbl_Corrections c

	LEFT OUTER JOIN

	tbl_ImportExtAG iea

	ON

	c.FieldTagnum = iea.FieldTagnum

	WHERE 

	iea.FieldTagnum IS NULL

Open in new window


Is it the complete script?

Without your tables and data I can't offer any other insight regarding your question.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

rtayIT DirectorAuthor Commented:
Simplified table structure

Tbl_importEXTAG
       ImprtID int
       Fieldtagnum nvarchar
       Trucknumber. Nvarchar
       OtherInfo Nvarchar

Tbl_corrections
      CorrID int
      Fieldtagnum nvarchar
      Trucknumber nvarchar

Tables are exact same structure.  Data comes from flat file through ssis.  Corrections to data come in same flat file a few days later and when duplicate fieldtagnum is found it directs the row to the corrections table.  Corrections need to be reviewed and then overwrite selected fields such as truck number when user triggers the sp.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Give us some sample data and tell us which record is updated now and which one should be really updated.
rtayIT DirectorAuthor Commented:
Sample data:

tbl_ImportExtAG     imprtID         fieldTagNum    TruckNumber
                                        11                  12345                     1
                                         12                 12346                      4
                                        13                  12456                       2

tbl_Corrections      CorrID          fieldTagNum       TruckNumber
                                     45                     12345                       1
                                     46                     12346                        3
                                    47                      12456                        7
                                    48                      23456                        4


So, tbl_Corrections needs to check tbl_ImportExtAG for matching fieldtagnum.  if exists, replace the TruckNumber value in tbl_ImportExtAg with the value in the TruckNumber field in tbl_Corrections.  If fieldtagnum does not exist in tbl_importextag, then it inserts the record from tbl_Corrections.  

The SP I wrote works, but it is just changing the trucknumber in the first row of data and stops.  I need it to go through all records and change the value.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try to swap the FROM and INNER JOIN tables:
UPDATE iea
  SET iea.TruckNumber=c.TruckNumber
  FROM tbl_ImportExtAG iea 
  INNER JOIN tbl_Corrections c ON c.FieldTagnum = iea.FieldTagnum

Open in new window

Scott PletcherSenior DBACommented:
The stored procedure below works, but only updates the first row that meets the where clause.

That's not logical, since there's nothing in the code to limit the UPDATE to a single row [I guess barring an obscure and exceptionally rare use of "SET ROWCOUNT" not shown in the code].

Is there a trigger(s) on the table that might interfere with the UPDATE and/or the INSERT?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtayIT DirectorAuthor Commented:
thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.