8

I have two tables,

Database 1:

Table 1:

  • Id -001(int)

  • Name -xxx(varchar)

  • Male -0 (bit)

  • Female-1 (bit)

Database 2:

Table 2:

  • Id -1 (int)

  • Ca.Name-yyy (varchar)

  • Gender - M or F (varchar)

These are my tables. I want to update data from database2, Table 2 to database1, table1. So i create update query. But I have problem with Gender field

Select 'UPDATE T1
 SET T1.MALE ='+ CASE WHEN r.Gender = 'M' THEN 0 ELSE 1 END

 FROM T2 As r

In the below code, what I starred I have a doubt how to use if statement here. Any one understand rectify my problem?

Duk
  • 905
  • 5
  • 15
  • 34

2 Answers2

6

Use case when for this purpose.

Follow this question :

SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported

CASE  
  WHEN GENDER='M' THEN 0 
  ELSE 1 
END

This can be in your case.

For more syntax details: CASE (Transact-SQL)

Community
  • 1
  • 1
Freelancer
  • 9,008
  • 7
  • 42
  • 81
1

Likewise, you need to use CASE WHEN. The query would look like:

UPDATE T1 
SET T1.MALE = CASE WHEN T2.caname = 'm' THEN 0 ELSE 1 END,
    T1.female = CASE WHEN T2.caname = 'm' THEN 1 ELSE 0 END  // you also need update this otherwise a person would end up with two genders :)
FROM TABLE1 T1
INNER JOIN table2 T2 ON T2.ID = T1.ID

SQL FIDDLE DEMO:

Praveen Nambiar
  • 4,852
  • 1
  • 22
  • 31
  • I have error like "Conversion failed when converting the varchar value 'UPDATE Jobs SET T1.MALE =' to data type int." – Duk Apr 13 '13 at 07:06
  • in your question can you also post the `DATATYPE` of columns...like if its int, varchar, bit, etc...or post the entire error – Praveen Nambiar Apr 13 '13 at 07:09
  • "Conversion failed when converting the varchar value 'UPDATE Jobs SET T1.MALE =' to data type int." – Duk Apr 13 '13 at 07:26
  • i hope u r not using the select statement with the update statement...like in the question you have shown..it not the correct way...do it the way i have shown in the fiddle demo..first update then use select. – Praveen Nambiar Apr 13 '13 at 07:30