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

Scott Pletcher 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

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Syntax 5 33 and sql server 4 33
Find results from sql within a time span 11 29
Create snapshot on MSSQL 2012 3 16
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

816 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

12 Experts available now in Live!

Get 1:1 Help Now