Solved

SQL Update ALL FIELDS in a table

Posted on 2013-01-25
5
414 Views
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
0
Comment
Question by:swaggrK
  • 3
  • 2
5 Comments
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 200 total points
ID: 38819522
UPDATE dbo.tablename
SET
    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
WHERE
    --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', ... )
0
 

Author Comment

by:swaggrK
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
SET
    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
WHERE
    a_m1 IN ( 'yy') OR
    b_m2 IN ( 'yy') OR
    c_m1 IN ( 'yy') OR
    c_m2 IN ( 'yy') 

Open in new window

0
 
LVL 69

Expert Comment

by:ScottPletcher
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:

SET
    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
0
 

Author Comment

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

Author Closing Comment

by:swaggrK
ID: 38819871
Very helpful and responsive.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

747 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

10 Experts available now in Live!

Get 1:1 Help Now