Stored procedure to update table

Hi Experts,
I have  table where i need to update one column with values that are distinct from another column. Really bad at explaining things but i guess i could illustrate it more below...
This is my original table.
DBO.myTable
Col1     Col2   Col3
1          A001   NULL
2          A001   NULL
3          A001   NULL
4          A002   NULL
5          A003   NULL
6          A003   NULL
7          A004   NULL
8          A005   NULL
9          A006   NULL
10        A006   NULL
...What i wanted was to create a stored procedure which updates column 3 with value of column 1.
My desired output table should looks like this...

DBO.myTable
Col1     Col2   Col3
1          A001   1
2          A001   1
3          A001   1
4          A002   4
5          A003   5
6          A003   5
7          A004   7
8          A005   8
9          A006   9
10        A006   9
 Is this possible(with just 1 stored procedure)? If Yes, would really appreciate and code sample or guidance.
jsuanqueAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
You dont really need a stored procedure for this but in case, you could write it as

CREATE PROCEDURE prcUdateCol
AS
BEGIN
      update myTable
        set Col3 = (select MIN(Col1)
                   from myTable B
                   where B.Col2 = myTable.Col2
                   group by B.Col2)
END
GO
0
 
Ephraim WangoyaCommented:
update myTable
set Col3 = (select MIN(Col1)
                   from myTable B
                   where B.Col1 = myTable.Col1
                   group by B.Col2)
0
 
Ephraim WangoyaCommented:
Sorry, the join is on col2

update myTable
set Col3 = (select MIN(Col1)
                   from myTable B
                   where B.Col2 = myTable.Col2
                   group by B.Col2
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Anthony PerkinsCommented:
Something like this perhaps:
SET     Col3 = d.MinCol1
FROM    MyTable t
        INNER JOIN (SELECT  Col2,
                            MIN(Col1) MinCol1
                    FROM    dbo.MyTable
                    GROUP BY Col2
                   ) d ON t.Col2 = d.Col2

Open in new window

0
 
Anthony PerkinsConnect With a Mentor Commented:
Let's try that again:
UPDATE  t
SET     Col3 = d.MinCol1
FROM    MyTable t
        INNER JOIN (SELECT  Col2,
                            MIN(Col1) MinCol1
                    FROM    dbo.MyTable
                    GROUP BY Col2
                   ) d ON t.Col2 = d.Col2

Open in new window

0
 
jsuanqueAuthor Commented:
Hello Ewangoya/Acperkins,
Both solution works. Thanks heaps
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.