0

I am just learning to code SQL. Can someone help me with an update statement, I have done the insert but am stuck with an update. My table prime_test has two columns number and is_prime

Here is the question: Write one or more update statements that sets the 'is_prime' column for each associated integer. For a prime, the column should read 'prime'. For non-primes, the column should read 'composite'.

My code

    CREATE TABLE `sql_inventory`.`prime_test` (
      `number` INT NOT NULL,
      `is_prime` VARCHAR(50) NULL,
      PRIMARY KEY (`number`));

    INSERT INTO `prime`.`prime_test`
    (`number`)
    VALUES
    (rand()*(100-2+1)+2);

Function to find the prime numbers:

CREATE FUNCTION isPrime(@num int)
RETURNS VARCHAR(50)
BEGIN
    DECLARE @prime_or_notPrime INT
    DECLARE @counter INT
    DECLARE @retVal VARCHAR(50)
    SET @retVal = 'composite'
    SET @prime_or_notPrime = 1
    SET @counter = 2
    WHILE (@counter <= @number/2 )
    BEGIN
        IF (( @number % @counter) = 0 )
        BEGIN
            set @prime_or_notPrime = 0
            BREAK
        END

        IF (@prime_or_notPrime = 1 )
        BEGIN
            SET @retVal = 'prime'
        END
        SET @counter = @counter + 1
    END
    return @retVal
END
update prime_test 
set is_prime = dbo.isPrime(select number From 'prime'.'prime_test')
  • There's no built-in function in SQL to check if a number is prime. You need to write a stored function to do this. – Barmar May 11 '22 at 16:52
  • 1
    You can adapt the sql server solution https://stackoverflow.com/questions/15566619/sql-prime-number-function – Serg May 11 '22 at 17:47
  • @serg Thanks for the link. I did create a function but it is not accepting variable num inside as an argument. I have the function to my question as well. –  May 11 '22 at 21:30
  • To the function, there are many missing `;` terminators. An `IF` does NOT have a begin/end block, but needs a `THEN` and an `END IF`. The update SQL, if intended to be part of the function, is _outside_ of the function's definition. Also, you will need a `delimiter` when creating a function. – Paul T. May 12 '22 at 02:01
  • Can just update the is_prime column using the update statement to find the prime numbers? –  May 12 '22 at 18:23

0 Answers0