MariaDB Error Explained: "wrong syntax to use near 'declare exists_check int'"

• "wrong syntax": This indicates that the MariaDB server encountered an instruction that it doesn't recognize or understand based on its grammar rules.
• "near 'declare exists_check int'": This points to the specific part of the code where the error likely originates. In this case, it's near the line that starts with declare exists_check int.

Problem:

The issue lies in using the DECLARE statement outside of a stored procedure or function body in MariaDB. MariaDB doesn't natively support declaring variables within regular SQL statements.

Solution:

To declare variables and use them in your MariaDB code, you need to create a stored procedure or function. Here's the general structure:

DELIMITER // -- Define a custom delimiter (optional, but recommended for clarity)

CREATE PROCEDURE procedure_name (parameters)
BEGIN
  DECLARE exists_check INT; -- Declare your variable(s)
  -- Your SQL statements using exists_check and other variables
END //

DELIMITER ; -- Reset the delimiter to the default semicolon

Explanation:

1. DELIMITER //: This line is optional but helpful for complex stored procedures. It allows using a different delimiter (like //) instead of the standard semicolon (;) within the procedure definition. This prevents conflicts with semicolons inside your code.
2. CREATE PROCEDURE procedure_name (parameters): This line defines a new stored procedure named procedure_name that can optionally accept input parameters.
3. BEGIN: This marks the start of the procedure body where you can write your SQL statements.
4. DECLARE exists_check INT;: Now, you can declare your variable exists_check with the data type INT (integer) inside the procedure.
5. -- Your SQL statements using exists_check and other variables: This is where you write your actual SQL code that might use the exists_check variable and other variables you declare.
6. END //: This line marks the end of the procedure body.
7. DELIMITER ;: If you used a custom delimiter, reset it back to the default semicolon.

Alternative (if stored procedures aren't an option):

• If creating stored procedures isn't feasible, consider using temporary tables or subqueries to achieve similar logic without declaring variables directly in your SQL statements.

DELIMITER // -- Define a custom delimiter

CREATE PROCEDURE check_user_points (user_id INT) -- Optional parameter: user ID
BEGIN
  DECLARE exists_check INT; -- Declare a variable to store check result

  SELECT COUNT(*) INTO exists_check -- Count points for the user
  FROM user_points
  WHERE user_id = user_id; -- Use the input parameter if provided

  IF exists_check = 0 THEN -- Check if user has any points
    INSERT INTO user_points (user_id, points) VALUES (user_id, 10); -- Add default points
  END IF;
END //

DELIMITER ; -- Reset the delimiter to semicolon

• This procedure takes an optional user_id parameter to check points for a specific user.
• It declares a variable exists_check to store the count result.
• It checks if the user has any points (exists_check = 0) and adds default points (10 in this case) if not.

Alternative Using a Subquery (without Stored Procedure):

INSERT INTO user_points (user_id, points)
SELECT user_id,
       CASE -- Conditional expression to check and set default points
         WHEN (SELECT COUNT(*) FROM user_points WHERE user_id = user_id) = 0 THEN 10
         ELSE 0 -- Existing users won't get additional points
       END AS points
FROM users; -- Assuming a table named "users" with user IDs

• This code directly inserts data into user_points.
• It uses a CASE expression to check for existing points using a subquery inside the SELECT statement.
• If no points exist (COUNT(*) = 0), it sets points to 10. Existing users won't get additional points (ELSE 0).

• This approach allows you to create variables outside of stored procedures, but they exist only within the current client session.
• Syntax: SET @variable_name = value; (e.g., SET @total_count = 0;)
• Use: This can be helpful for temporary calculations or tracking values across multiple non-procedural queries within the same session.

Example:

SET @product_id = 123;
SELECT name, price FROM products WHERE product_id = @product_id;

• Limitation: These variables are not persistent and disappear when the session ends.

Temporary Tables:

• Create a temporary table to store intermediate results or data used for calculations.
• Syntax: CREATE TEMPORARY TABLE temp_table (column1 datatype, column2 datatype);
• Use: This method can be useful if you need to perform complex operations on the data before using it in your final query.

CREATE TEMPORARY TABLE filtered_products (product_id INT, name VARCHAR(255));
INSERT INTO filtered_products (product_id, name)
SELECT product_id, name FROM products WHERE category = 'electronics';

SELECT * FROM filtered_products;
DROP TEMPORARY TABLE filtered_products;

• Limitation: Temporary tables can potentially impact performance compared to variables, especially for large datasets.

Subqueries:

• Use subqueries to perform calculations or filtering within the main SELECT statement.
• This can sometimes eliminate the need for a separate variable altogether.

SELECT product_id, name, price * (1 - discount/100) AS discounted_price
FROM products;

• Limitation: Subqueries can become complex and less readable, especially for intricate logic.

Choosing the Best Method:

• If you need variable-like functionality within a single session and reusability isn't a concern, user-defined variables can be a quick option.
• For more complex calculations or temporary data storage, temporary tables offer flexibility.
• When possible, using subqueries can streamline your code and potentially enhance readability.
• Stored procedures remain the most robust and reusable approach for complex logic involving variables.