question

technette avatar image
technette asked

update stored procedure

I have a process that runs regular updates to tables. I had to create another table which is a subset of one of the tables and need to update that table when the first table is finished updating. How would I write the new update stored procedure? The first table does have a last updated column. UPDATE [Databse].[dbo].[Table] SET [Column1] = ,[Column2] = ,[Column3] = ,[SortFlag] = WHERE
update
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
@technette from what you just wrote in the comments to @SirSql's answer, you have made a mistake in the T-SQL update command. You have `Table2` which is your main table and `StandTable` which contains the subset of data. You have a bulk update of `Table2` each day from your AS/400 system. You then want the matching data in `StandTable` to be updated accordingly. You need to take your code and change the inequality to an equality: UPDATE StandTable SET Col1 = Table2.Col1, Col2 = Table2.Col2, Col3 = Table2.Col3 FROM Table2 INNER JOIN StandTable ON Table2.Col2 = StanTable.Col2 -- << here I changed the join to be an equality This will find all matches in the tables and update the table `StandTable` and all should be fine.
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette avatar image technette commented ·
Ohh! I was trying to do two things at once... Find where the code in both tables don't match and populate the subtable with any new values. How will I populate the table with new values?
0 Likes 0 ·
technette avatar image technette commented ·
William Thank you! I also added the following after the update to include new records: INSERT INTO StandardTable SELECT col1, col2, Col3, Col4 From Table2 where Table2.Col1='StdName' AND not exists (select * from Table2 where Table2.Col2 = Col2 )
0 Likes 0 ·
SirSQL avatar image
SirSQL answered
Is your first process a stored procedure? If so it would make most sense to handle both updates within a single transaction so that either both succeed or fail. BEGIN TRY BEGIN TRAN UPDATE TableA SET Col1 = x WHERE Val = y UPDATE TableB SET Col1 = x where Val = y COMMIT TRAN END TRY BEGIN CATCH SELECT ERROR_MESSAGE() ROLLBACK TRAN If you are performing large set based operations then maybe an update based upon a join with the last updated information. Finally (although I hate to say this) you could use a trigger which performs an update of the second table. Ensure that you have solid FK relationships either way.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

technette avatar image technette commented ·
SirSQL Thank you for responding. The first table is update using an old process. feeds come in weekly from an AS400 database into one table and then the other tables are updated. I was asked to create a stored procedure that could be process through a job.
0 Likes 0 ·
SirSQL avatar image SirSQL commented ·
What process performs the updates on the tables after the staging table has been loaded from the AS400, or is that what you are trying to accomplish?
0 Likes 0 ·
technette avatar image technette commented ·
I would like to just Update the new table when the other process is complete. I don't know all that is involved in the major update process. I just want a job to run executing my update procedure on a weekly basis. so I created the following in an update stored procedure but when I execute. I get an entired database table of the second record. It updates the first only then duplicates all the rows with the same record. UPDATE StandTable SET Col1 = Table2.Col1, Col2 = Table2.Col2, Col3 = Table2.Col3 FROM Table2 INNER JOIN StandTable ON Table2.Col2 StanTable.Col2
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.