SQL Update ALL FIELDS in a table

Posted on 2013-01-25
Medium Priority
Last Modified: 2013-01-25
I have a table that contains wrong values in all fields that end with _m1
I would like to write an update query that populates ALL fields ending with _m1 all at once
instead of doing it manually.

Ideally, I would like to do something like this..

Update entire_table
SET _m1 = 'ab'
where _m1 = 'yy'

Example of my table structure:

o_id     a_m1        b_m2     c_m1     c_m2
1          ab            yy           ab          yy
2          yy            NULL      ab          NULL
3          ab            yy           yy          NULL
Question by:swaggrK
  • 3
  • 2
LVL 70

Accepted Solution

Scott Pletcher earned 800 total points
ID: 38819522
UPDATE dbo.tablename
    a_m1 = CASE WHEN a_m1 IN ( 'yy', ... ) THEN 'ab' ELSE a_m1 END,
    b_m2 = CASE WHEN b_m1 IN ( 'yy', ... ) THEN 'ab' ELSE b_m2 END,
    c_m1 = CASE WHEN c_m1 IN ( 'yy', ... ) THEN 'ab' ELSE c_m1 END,
    c_m2 = CASE WHEN c_m2 IN ( 'yy', ... ) THEN 'ab' ELSE c_m2 END
FROM dbo.tablename
    --make sure at least one column needs changed
    a_m1 IN ( 'yy', ... ) OR
    b_m2 IN ( 'yy', ... ) OR
    c_m1 IN ( 'yy', ... ) OR
    c_m2 IN ( 'yy', ... )

Author Comment

ID: 38819728
Ok, I think I am doing something wrong because when I run your code, both _m1 and _m2 fields are being updated to 'ab' where 'yy' exist.

Here is what I ran...

UPDATE tablename
    a_m1  = CASE WHEN m1  IN ( 'yy') THEN 'ab' ELSE m1  END,
     b_m2 = CASE WHEN  b_m2 IN ( 'yy') THEN 'ab' ELSE  b_m2 END,
     c_m1 = CASE WHEN  c_m1 IN ( 'yy') THEN 'ab' ELSE  c_m1 END,
    c_m2 = CASE WHEN c_m2 IN ( 'yy') THEN 'ab' ELSE c_m2 END
FROM tablename
    a_m1 IN ( 'yy') OR
    b_m2 IN ( 'yy') OR
    c_m1 IN ( 'yy') OR
    c_m2 IN ( 'yy') 

Open in new window

LVL 70

Expert Comment

by:Scott Pletcher
ID: 38819771
You need to put ALL desired conditions for each column in the "WHEN".  And specify the appropriate new value after "THEN".

For example ONLY -- change as needed to match your data:

    a_m1  = CASE WHEN a_m1  IN ( 'yy') THEN 'ab' ELSE m1  END,
    b_m2 = CASE WHEN a_m1 NOT IN ('yy') AND b_m2 IN ( 'yy') THEN 'ab' ELSE  b_m2 END,
    c_m1 = CASE WHEN c_m1 IN ( 'yy') THEN 'ab' ELSE  c_m1 END,
    c_m2 = CASE WHEN c_m1 NOT IN ('yy') AND c_m2 IN ( 'yy') THEN 'ab' ELSE c_m2 END

Author Comment

ID: 38819867
THANKS for the explanation. That helped. Got it to work now. Thanks!!!

Author Closing Comment

ID: 38819871
Very helpful and responsive.

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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.

Join & Write a Comment

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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.

627 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