• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

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.
0
jsuanque
Asked:
jsuanque
  • 3
  • 2
2 Solutions
 
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
 
Ephraim WangoyaCommented:
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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 PerkinsCommented:
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now