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
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
  • 2
LVL 69

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 69

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

771 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