Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Statement to Update Rows on Condition

Posted on 2013-12-09
4
Medium Priority
?
264 Views
Last Modified: 2013-12-09
I have duplicate rows in a table. All column values are equal except one numeric column.
I need to set a column value for the lowest Col3 value in each group.

Col1         Col2           Col3             Col4

1808      AA      30175130          1   Set Col4 to 0 for the min Col3 value of the AA Group
1808      AA      30176132          1
4450      AAN      30175454          1   Set Col4 to 0 for the min value of the AAN Group
8384      AAN      30175787          1
8385      AAN      30175788          1
4450      AAN      30177864          1
8384      AAN      30178679          1
8385      AAN      30178680          1

How can this be done?

Thanks,
0
Comment
Question by:Dovberman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39706084
Give this a whirl... a JOIN on your_table (rename to suit your needs) and a subquery that gets the minimum value of Col3 based on the Col2 value.
UPDATE your_table
SET Col4 = 0
FROM your_table yt
   JOIN (SELECT Col2, Min(Col3) as Col3_min
            FROM your_table
            GROUP BY Col2) yt_min 
   ON yt.Col2 = yt_min.Col2 AND yt.Col3 = yt_min.Col3_min

Open in new window

0
 

Author Comment

by:Dovberman
ID: 39706234
Thank you,

I will try this later today and reply.
0
 

Author Comment

by:Dovberman
ID: 39706532
Works perfectly,

Thanks,
0
 

Author Closing Comment

by:Dovberman
ID: 39706534
Works perfectly,

Thanks,
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question