May 6, 2016 at 1:45 pm
Hi All,
I need to update two tables based on the third table ID. I am passing an ID of table 1 from my code as string with comma delimited so '1,2,3' and want to update two tables based on that ID. I have below tables:
Table1
ID Tbel2ID Tabl3ID
1 5 66
2 23 76
3 46 17
Table2
ID Col2
5 Test1
23 Test2
46 Test3
88 Test4
99 Test5
Table3
ID Col2
66 Test5
88 Test11
54 Test10
76 Test3
17 Test65
I need to update Col2 of Table2 and Col2 of Table3 and I am passing Table1 ID's as a parameter in the stored procedure so if I am passing 1,2,3 and Col2 value for table2 as "Test6" and Col2 value for table3 as Test100 all the 5,23,46 ID's for Table2 to be changed to Test6 and 66, 76, 17 to be changed to Test 100 so the final table will look like this:
[Code]
Table2
ID Col2
5 Test6
23 Test6
46 Test6
88 Test4
99 Test5
Table3
ID Col2
66 Test100
88 Test11
54 Test10
76 Test100
17 Test100
[/code]
The stored proc will be something like this:
Create procedure dbo.Sp_test
(
@ID int
@Table1Col1Value varchar(200),
@Table1Col2Value varchar(200)
)
any help will be appreciated.
May 6, 2016 at 1:50 pm
anjaliagarwal5 (5/6/2016)
Hi All,I need to update two tables based on the third table ID. I am passing an ID of table 1 from my code as string with comma delimited so '1,2,3' and want to update two tables based on that ID. I have below tables:
Table1
ID Tbel2ID Tabl3ID
1 5 66
2 23 76
3 46 17
Table2
ID Col2
5 Test1
23 Test2
46 Test3
88 Test4
99 Test5
Table3
ID Col2
66 Test5
88 Test11
54 Test10
76 Test3
17 Test65
I need to update Col2 of Table2 and Col2 of Table3 and I am passing Table1 ID's as a parameter in the stored procedure so if I am passing 1,2,3 and Col2 value for table2 as "Test6" and Col2 value for table3 as Test100 all the 5,23,46 ID's for Table2 to be changed to Test6 and 66, 76, 17 to be changed to Test 100 so the final table will look like this:
[Code]
Table2
ID Col2
5 Test6
23 Test6
46 Test6
88 Test4
99 Test5
Table3
ID Col2
66 Test100
88 Test11
54 Test10
76 Test100
17 Test100
[/code]
The stored proc will be something like this:
Create procedure dbo.Sp_test
(
@ID int
@Table1Col1Value varchar(200),
@Table1Col2Value varchar(200)
)
any help will be appreciated.
Wow this is incredibly vague. It seems you need to parse your delimited list of values. You can find a great splitter by following the link in my signature. A better approach than that would be to use a table valued parameter instead of a delimited list. Either way you are going to end up with a table. Then you just need to use that table and join to your other tables for the update.
If you need help with the actual code you will need to provide a lot more details than what we have seen so far. take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 6, 2016 at 2:21 pm
I am not looking for splitter function. I am also looking for update statement that will update two other tables table 2 and Table 3 based on the ID provided by table1. I thought I explained really well.
May 6, 2016 at 2:30 pm
anjaliagarwal5 (5/6/2016)
I am not looking for splitter function. I am also looking for update statement that will update two other tables table 2 and Table 3 based on the ID provided by table1. I thought I explained really well.
You explained the situation but what you are asking for is code right? I can't provide any code that is actually tested unless I spend a bunch of time turning your post into tables and data.
As such I will try to help steer you in the right direction. You will need to use 2 update statements. Each of those statements will need to join from Table1 to either Table2 or Table3 to get the new values.
If you can be troubled to post something consumable as I previously suggested I will be happy to help with the query.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply