run a query using EXECUTE BLOCK to prepair a column for an unique-Index

Question

I have a column in a table that I want to use for an unique-index.

My script should make the data unique by concat the id of a record, if the data of current record is duplicate.

I've learned that I need to put this inside a EXECUTE BLOCK.

EXECUTE BLOCK 
AS 
    DECLARE VARIABLE ID BI;
    DECLARE VARIABLE REASON XTXT;
    DECLARE VARIABLE LAST_REASON XTXT = '';

BEGIN
FOR SELECT 
            ID_STATEMENT, 
            REASON 
    FROM 
            STATEMENT
    WHERE
            ID_STATEMENT > 0
    ORDER BY
            REASON ASC
    INTO    :ID, 
            :REASON
DO BEGIN
IF ( REASON = LAST_REASON ) THEN
    UPDATE
          STATEMENT
    SET 
          REASON = :REASON || ' X' || :ID
    WHERE
          ID_STATEMENT = :ID;
END
LAST_REASON = REASON;
END

But I am getting an error-message:

-104 Dynamic SQL Error SQL error code = -104 Token unknown - line 18, column 13

In line 18 starts the into-part, column 13 is the ':' in front of :ID,

I try this under PHP, driver is PDO.

Answer 1

I've gotten a little further now.

The syntax problem with the colon has been resolved.

The reason I keep getting an error message is because of the colon in the INTO line is a bug in the PDO-driver I use under PHP.

Purely by coincidence, while researching another topic, I have learn that this bug has existed since PHP version 5.6 and at least not yet been eliminated by version 7.3.

This "EXECUTE BLOCK" is running very well:

EXECUTE BLOCK 
AS 
    DECLARE VARIABLE ID BI;
    DECLARE VARIABLE REASON XTXT;
    DECLARE VARIABLE LAST_REASON XTXT = '';
BEGIN
      FOR SELECT 
                  ID_STATEMENT, 
                  REASON 
          FROM 
                  STATEMENT
          WHERE
                  ID_STATEMENT > 0
          ORDER BY
                  REASON ASC
          INTO    :ID, 
                  :REASON
      DO 
        BEGIN
              IF (REASON = LAST_REASON) THEN BEGIN
                  UPDATE
                        STATEMENT
                  SET 
                        REASON = :REASON || ' X' || :ID
                  WHERE
                        ID_STATEMENT = :ID;
              END
              LAST_REASON = REASON;
        END
END

Driver: PHP Interbase- / Firebird-Driver

Important is the "BEGIN" after the "THEN" in the IF-Statement !!!

Answer 2

As far as I understand you want to make field REASON unique. In this case it is simple:

EXECUTE BLOCK 
AS 
    DECLARE VARIABLE ID BLOB;
    DECLARE VARIABLE REASON XTXT;

BEGIN
FOR SELECT 
            REASON || LIST(ID_STATEMENT, 'X'),
            REASON 
    FROM 
            STATEMENT
    WHERE
            ID_STATEMENT > 0
    GROUP BY
            REASON
    HAVING
            COUNT(*) > 1
    INTO    :ID, 
            :REASON
DO BEGIN
    DELETE FROM
          STATEMENT
    WHERE
          REASON = :REASON;
    INSERT INTO
          STATEMENT
    VALUES
          (:ID, :REASON);
END
END

After that in the table you'll have records with unique REASONs and concatenated IDs.