Using IF's within an UPDATE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Using IF's within an UPDATE

Author

Topic

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 04:56:39

Hi all, first post so be nice :)

I'm trying to insert values into a table (from a linked server - a .XLS file). One of the fields on the .XLS file is a text field which contains values: Y or N.

I am trying to insert these into a bit type coumn in a table. Is there anyway i can convert these to 1 or 0? the code i had though was along the lines of:

INSERT INTO EMPLOYEE (
EMPLOY_REF,
EMPLE_NOT_MN)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
if (select EMPLE_NOT_MN from mockdownload_dd...[EMPLOYEE$]) = 'Y'
begin
'1' --INSERT THE VALUE 1 TO EMPLOYEE
end
else
begin
'2' --INSERT THE VALUE 1 TO EMPLOYEE
end
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL

Thanks in advance people.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 04:59:41

you can't use IF .. ELSE here. user CASE statement instead.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-08-07 : 05:01:34

Insert into Employee(...)
select case EMPLE_NOT_MN when 'Y' then 1 else 0 end
from mockdownload_dd...[EMPLOYEE$]
....

Harsh Athalye
India.
"Nothing is Impossible"

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 05:40:26

Hi. Thanks for the reply.......however I am still a lttle confused by the syntax. I tried the following but it didnt like it:

INSERT INTO EMPLOYEE (
EMPLOY_REF,
EMPLE_NOT_MN,
NOTICE,
SORT_CODE,
NI_NUMBER)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL

Thanks again people.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-07 : 05:43:39

Try This..


INSERT INTO EMPLOYEE (EMPLOY_REF,EMPLE_NOT_MN,NOTICE,SORT_CODE,NI_NUMBER)
SELECT
CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end As EMPLE_NOT_MN,
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$]
WHERE EMPLOY_REF NOT IN (SELECT EMPLOY_REF FROM EMPLOYEE)
AND EMPLOY_REF IS NOT NULL

If this query is genric then you can create the UDF, passing
EMPLE_NOT_MN to it and return 1 or 0 based on the selection

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 05:58:30

[code]
INSERT INTO EMPLOYEE ( EMPLOY_REF, EMPLE_NOT_MN, NOTICE, SORT_CODE, NI_NUMBER) -- You have 5 columns here
SELECT CAST(EMPLOY_REF AS varchar(10)),
CASE EMPLE_NOT_MN when 'Y' then 1 else 0 end, -- missing comma
-- But you only have 4 in your select statement. You miss out NOTICE
CAST(SORT_CODE AS varchar(8)),
CAST(NI_NUMBER AS varchar(9))
FROM mockdownload_dd...[EMPLOYEE$] e
WHERE NOT EXISTS (SELECT * FROM EMPLOYEE x WHERE x.EMPLOY_REF = e.EMPLOY_REF)
AND EMPLOY_REF IS NOT NULL[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-07 : 06:00:46

opps good check TAN

Chirag

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-07 : 06:02:43

"I tried the following but it didnt like it:"
Gavin, you should post the error message. It will be easier for us to catch the mistake.

GavinD1977
Yak Posting Veteran

83 Posts

Posted - 2006-08-07 : 06:10:10