SQL Update ALL FIELDS in a table

Posted on 2013-01-25
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 69

Accepted Solution

ScottPletcher earned 200 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

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

867 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now