1

I have created a new view in my MariaDB database using phpMyAdmin combining the #_content and #content_frontpage tables called #__content_with_featured.

I am now attempting to add a new column to this view. Ultimately, it will contain calculated data based on other values from columns in the view, but at this stage I can't even add the column. I get an error referencing the second line:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ADD #_content_with_featured.f_current' at line 2

I have tried it with and without the view name. This is my SQL with the full new column name:

  1. ALTER VIEW #_content_with_featured AS

  2. ADD #_content_with_featured.f_current

What am I missing or doing wrong, please?

1 Answer 1

1

I solved it by using CREATE OR REPLACE VIEW rather than ALTER VIEW, like this:

#_content_with_featured_two AS 
SELECT #_content.id AS f_id,
       #_content.title,
       #_content.featured,
       #_content.images,
       #_content_frontpage.featured_up,
       #_content_frontpage.featured_down,
       CASE
           WHEN #_content.featured = '0' THEN 'No'
           WHEN #_content.featured = '1' THEN 'Yes'
       END AS f_current
FROM #_content
LEFT JOIN #_content_frontpage ON #_content.id = #_content_frontpage.content_id

Note: the actual CASE query isn't what I'm trying to achieve, that's there for demo purposes only.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.