?
Solved

one sql update statement

Posted on 2007-07-26
3
Medium Priority
?
215 Views
Last Modified: 2010-03-19
i have to tables: table1 and table2. i need to update a column in table1 with either 1 or 0 based on column in table2. that is, if table2.column = C then table1.column = 0
                       if table2.column = P then table1.column = 1

how do i write one sql update statement that would do it?
0
Comment
Question by:jgordin
  • 2
3 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19575160
update table1
  set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
from table1 t1
join table2 t2
  on t2.key = t1.key
0
 

Author Comment

by:jgordin
ID: 19575885
thanks. what would be the syntax if i need to set more than two fields:
i tried to add column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
but it doesnt work.

update table1
  set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
        column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
from table1 t1
join table2 t2
  on t2.key = t1.key
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19576014

update table1
  set column = CASE when t2.column = 'C' then 0 when t2.column = 'P' then 1 else null end
      , column2 = CASE when t2.column = 'E' then 0 when t2.column = 'A' then 1 else null end
from table1 t1
join table2 t2
  on t2.key = t1.key

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Loops Section Overview

840 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