January 11, 2011 at 12:40 pm
I have an Insert statement. One of the columns to be added will be either 'Y' or 'N' depending on the value of another queried column.
Pseudocode would be something like this, where I need to insert into field 'valid' based on the value of description.
Insert into table
values (@ssn,@empid,@race,
CASE @description
WHEN 'deputy' THEN valid = 'Y'
WHEN 'civilian' THEN valid ='N'
end
)
Thanks.
January 11, 2011 at 1:15 pm
You should be able to write it with the case statement as a field. So it would look like this:
insert into table
values (@ssn,@empid,@race,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end
)
The case itself is the field; so you do not have to set a value to return.
I hope this answers your question.
January 11, 2011 at 2:10 pm
The name of the field to be updated is different than the field which contains the determining value.
I need to update field 'valid' based on the value in field 'description'.
January 11, 2011 at 2:17 pm
If you need to include description and the valid column you would just need to add it into values list:
insert into table (ssn,empid,race,description,valid)
values (
@ssn,
@empid,
@description,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end
)
It shouldn't hurt anything to do that. Valid is a column in the same table right?
Hope to help,
Dane
January 11, 2011 at 2:41 pm
If I have values after the case statement what would it look like?
I'm not sure of the syntax. Also I don't have the columns hardcoded on the insert line because I'm putting data into all of them.
insert into table
values (
@ssn,
@empid,
@description,
CASE @description
WHEN 'deputy' THEN 'Y'
WHEN 'civilian' THEN 'N'
end,
column1,
column2,
)
January 11, 2011 at 2:46 pm
How you typed it seems correct. I am assuming that the trailing comma is a typo. There are hidden dangers with not specifying the columns you are inserting. If the table ever changes your insert will definitely fail unless it is also touched.
Hope this helps,
Dane
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply