Hello Everyone,
I have a requirement. I have two tables with following columns.
Table1 (emp_id, emp_sap_no, supervisor_id)
Table2 (emp_sap_no, spervisore_sap_no)
Both tables have thousands of records. Table1's supervisor_id column has 0 in it for all the records.
I need to update all supervisor_id in Table1.
i.e. I need to get the emp_id from Table1 using the Table2 (As it has the employee and supervisor mapping) then update same emp_id in supervisor_id column.
If you need more explanation kindly let me know
Kindly suggest.
I would start development on this by making it work with a SELECT statement, which you can do with 2 JOINs. The basic logic of it is you want to do something like:
SELECT [EMP].[emp_id], [EMP].[emp_sap_no], [supervisor].[emp_id]
FROM [Table1] AS [EMP]
JOIN [Table2] AS [mapping] ON [EMP].[emp_sap_no]=[mapping].[emp_sap_no]
JOIN [Table1] AS [supervisor] ON [supervisor].[emp_sap_no]=[mapping].[spervisore_sap_no]
Now that you have your select working and you can verify that it works as you expect (those are VERY important steps), I would change the SELECT to an UPDATE and you are good to go.
The main reason I recommend doing the SELECT first is that way you can validate that the data is accurate and what you expect prior to making the change to the table. In the event that the SELECT gives you bad data, you can correct it. My approach when making an update statement is to start with a SELECT, get the data to look like what I want, then change it to an UPDATE. There are some use cases where this is excessive obviously, but I like the added layer of safety by being able to validate the data prior to updating it.
I am making a few assumptions in the above. One big assumption is that spervisore_sap_no can map up to emp_sap_no.
I have not tested the above code as no sample data was provided. I just interpreted what you wrote and came up with a potential solution.
EDIT - updated code as the alias for the second table disappeared when posted but was visible while editing.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 13, 2021 at 3:56 am
Thank You Mr. Brian for your reply on my post.
Yes, it shows the correct data. Now I can update it.
Just one confusion, in update there is no WHERE clause, because I want to update all the data.
So will it affect the performance as there are around 25000 records and it will hit every 24 hours.
Kindly suggest.
Thank you
August 13, 2021 at 3:20 pm
I updated my post as the forum apparently didn't like the alias I used.
Since you are updating everything and you are doing it every 24 hours, I would recommend trying it out on a test system first to judge the performance and determine from there.
Is there a reason you are needing to reset that value daily on every row? What I mean is I imagine the supervisor of an employee doesn't change every day, and it is probably changing only a few entries per day. If you are truncating and reloading the data daily, it may be faster to just insert the new rows, delete the removed rows, and update the modified rows. Or, failing that, do you NEED to update that supervisor row? What I mean is, you could toss the query I provided into a view and then have end users and developers use the view to look at the data rather than updating that column every day?
Now as for the performance hit, that really depends on your system and indexes. If the 2 tables match up nicely (with foreign keys and primary keys), it should be pretty quick. But if you have 100's of indexes on the table, it may be slow. The best way to test performance is to actually run the query on a test system that is similar to your production systems. If you find that the performance is poor, you may want to update it in batches instead of the whole table at once.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply