Case Syntax Problem

[magicmarkuk]

Hi

I am having a problem with my syntax for the following code:

What I am trying to do is:

1. Check if sticky = 'y' then give a value of 1
2. If not then check whether announcement is 'y' then give a value of 2
3. If not then check whether announcement is 'a' then give a value of 3
4. If none of the above give a value of 0

I think one of my problems is changing from looking at sticky to announcement but there may be something more funamental.

INSERT INTO topics (
     topic_type
    )
    SELECT 
     t.sticky WHEN 'y' THEN 1
     t.announcement WHEN 'y' THEN 2
     t.announcement WHEN 'a' THEN 3
     ELSE 0
     END AS topic_type
    FROM setup t;

Many thanks
Mark

[magicmarkuk]

I should add (couldn't see an edit button) that I want to insert this into a SELECT statement ultimately so whether that changes the syntax?

Thanks
Mark

[pritaeas]

This should do it:

INSERT INTO topics (
  topic_type
)
SELECT 
  CASE
     WHEN sticky = 'y' THEN 1
     WHEN announcement = 'y' THEN 2
     WHEN announcement = 'a' THEN 3
     ELSE 0
  END CASE
FROM setup

[magicmarkuk]

Thank you very much works a treat :)