国产av日韩一区二区三区精品,成人性爱视频在线观看,国产,欧美,日韩,一区,www.成色av久久成人,2222eeee成人天堂

<p>I have two tables, a base table containing various information about the value 'tconst' (also the primary key) and another table linked to multiple 'tconst' values ??from 'nconst' under the name 'titleId' .</p> <p>---Base table 'titlebasics'</p> <table class="s-table"> <thead> <tr> <th>tconst</th> <th>Title Type</th> <th>...</th> </tr> </thead> <tbody> <tr> <td>tt0000009</td> <td>Movies</td> <td>...</td> </tr> <tr> <td>tt0000147</td> <td>Movies</td> <td>...</td> </tr> <tr> <td>...</td> <td>...</td> <td>...</td> </tr> </tbody> </table> <p>---Additional information table 'knownfortitles'</p> <table class="s-table"> <thead> <tr> <th>id</th> <th>nconst</th> <th>Title Order</th> <th>Title ID</th> </tr> </thead> <tbody> <tr> <td>1</td> <td>nm0000001</td> <td>1</td> <td>tt0050419</td> </tr> <tr> <td>2</td> <td>nm0000001</td> <td>2</td> <td>tt0053137</td> </tr> <tr> <td>...</td> <td>...</td> <td>...</td> <td>...</td> </tr> </tbody> </table> <p>The "problem" is that some values ??in <code>knownfortitles.titleId</code> do not exist in <code>titlebasics.tconst</code>. I want to create a stored procedure where I can pass as parameters the names of two tables and two corresponding columns. This process will first check to see if the value that does not exist in the first table actually exists in the second table, and if so, adds a value to the second table called <code>is_in_<base_table_name></code> List. It will then subsequently update this column with the boolean value from each row in the second table. I want to do this in a stored procedure because I have a lot of tables with this problem and I want to be able to use this procedure instead of writing the same code over and over with different values. However, I get an error when I try to call my program and I can't seem to fix it. </p> <p>As a stored procedure, this is where I get stuck.</p> <pre class="brush:php;toolbar:false;">CREATE PROCEDURE `CheckValueExistsInBaseTable`( IN checkedTable VARCHAR(100), IN referencedBaseTable VARCHAR(100), IN checkedCol VARCHAR(100), IN referencedCol VARCHAR(100) ) BEGIN DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable'; DECLARE sql_statement1 VARCHAR(1000) DEFAULT 'SELECT NULL;'; DECLARE sql_statement2 VARCHAR(1000) DEFAULT 'SELECT NULL;'; SET @new_column_name = CONCAT('is_in_',referencedBaseTable); -- Add new column to checked table if it doesn't exist SET @sql_statement1 = CONCAT('IF (SELECT CASE WHEN EXISTS( 選擇1 FROM ', checkedTable, ' WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL; ELSE SELECT NULL; END IF'); PREPARE stmt1 FROM @sql_statement1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1; -- Update is_in_referencedBaseTable column in checked table SET @sql_statement2 = CONCAT('UPDATE ', checkedTable, ' SET ', @new_column_name, ' = CASE WHEN EXISTS(SELECT * FROM ', referencedBaseTable, ' WHERE ', referencedBaseTable, '.', referencedCol, ' = ', checkedTable, '.', checkedCol, ') THEN 1 ELSE 0 END'); PREPARE stmt2 FROM @sql_statement2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2; END</pre> <p>無論我嘗試更改什么，這都會(huì)給我?guī)硪韵洛e(cuò)誤之一。</p> <blockquote> <p>錯(cuò)誤代碼：1064。您的 SQL 語法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對應(yīng)的手冊，了解在第 1 行“NULL”附近使用的正確語法</p> </blockquote> <p>或</p> <blockquote> <p>錯(cuò)誤代碼：1064。您的 SQL 語法有錯(cuò)誤；檢查與您的 MySQL 服務(wù)器版本相對應(yīng)的手冊，了解在 'IF (SELECT CASE WHEN EXISTS( 選擇1 來自知名作品 WHERE titleId NOT' 在第 1 行</p> </blockquote> <p>我還創(chuàng)建了測試程序來檢查哪些部分可能出現(xiàn)問題，但兩者都工作得很好，這讓我更加困惑。第一個(gè)僅返回我在 <code>CONCAT</code> 中放入的內(nèi)容，以查看其中是否存在任何語法錯(cuò)誤。</p> <pre class="brush:php;toolbar:false;">CREATE PROCEDURE `test`( IN checkedTable VARCHAR(100), IN referencedBaseTable VARCHAR(100), IN checkedCol VARCHAR(100), IN referencedCol VARCHAR(100), IN new_column_name VARCHAR (100) ) BEGIN -- Declaring the variable and assigning the value declare myvar VARCHAR(1000); DECLARE new_column_name VARCHAR(100) DEFAULT 'is_in_baseTable'; SET @new_column_name = CONCAT('is_in_',referencedBaseTable); SET myvar = CONCAT('IF (SELECT CASE WHEN EXISTS( 選擇1 FROM ', checkedTable, ' WHERE ', checkedCol, ' NOT IN (SELECT ', referencedCol, ' FROM ', referencedBaseTable, ')) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE ', checkedTable, ' ADD ', @new_column_name, ' BOOL; ELSE SELECT NULL; END IF'); -- Printing the value to the console SELECT concat(myvar) AS Variable; END</pre> <p>此過程給出下一個(gè)結(jié)果：</p> <pre class="brush:php;toolbar:false;">IF (SELECT CASE WHEN EXISTS( 選擇1 來自知名作品 WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL; ELSE SELECT NULL; END IF</pre> <p>這段代碼是正確的，我知道這一點(diǎn)是因?yàn)槲沂褂昧讼旅娴牡诙€(gè)過程，該過程利用了這個(gè)確切的代碼塊。</p> <pre class="brush:php;toolbar:false;">CREATE PROCEDURE `test2`() BEGIN IF (SELECT CASE WHEN EXISTS( 選擇1 來自知名作品 WHERE titleId NOT IN (SELECT tconst FROM titlebasics)) THEN 1 ELSE 0 END ) = 1 THEN ALTER TABLE knownfortitles ADD is_in_titlebasics BOOL; ELSE SELECT NULL; END IF; END</pre> <p>此過程將列 <code>is_in_titlebasics</code> 添加到表 <code>knownfortitles</code> 中，這就是我想要發(fā)生的事情，所以這很好。此時(shí)，我完全迷失了，不知道為什么我的實(shí)際存儲(chǔ)過程不起作用，因?yàn)樗旧鲜亲詈髢蓚€(gè)過程的組合。我暫時(shí)忽略了我希望存儲(chǔ)過程執(zhí)行的第二部分，因?yàn)槲矣龅降腻e(cuò)誤似乎將第一個(gè) <code>CONCAT</code> 語句視為問題。</p> <p>我希望這個(gè)問題是非常明顯的，但我只是忽略了。歡迎任何幫助，提前致謝！</p>

DELIMITER $$
USE `<schema>`$$
CREATE DEFINER=`Setupinfolab`@`%` PROCEDURE `CheckValueExistsInBaseTable`(
    IN checkedTable VARCHAR(100),
    IN referencedBaseTable VARCHAR(100),
    IN checkedCol VARCHAR(100),
    IN referencedCol VARCHAR(100)
    )
BEGIN
    SET @checkedTable = checkedTable;
    SET @referencedBaseTable = referencedBaseTable;
    SET @checkedCol = checkedCol;
    SET @referencedCol = referencedCol;
    SET @new_column_name  = CONCAT('is_in_', referencedBaseTable);
    
    -- Check if there are indeed values in checkedCol that are not present in referencedCol
    SET @query1 = CONCAT('
        SELECT CASE WHEN EXISTS(
            SELECT 1 
            FROM ',@checkedTable,'
            WHERE ',@checkedCol,' NOT IN (SELECT ',@referencedCol,' FROM ',@referencedBaseTable,')) 
            THEN 1 ELSE 0 END 
            INTO @proceed');
        
    -- Adding the boolean column
    SET @query2 = CONCAT('
        ALTER TABLE ',@checkedTable,' ADD ',@new_column_name,' BOOL;');
    
    -- Inserting boolean values in new column according to presence in referencedCol
    SET @query3 = CONCAT('
        UPDATE ',@checkedTable,' SET ',@new_column_name,' = (
            CASE WHEN EXISTS(
                SELECT * 
                FROM ',@referencedBaseTable,' 
                WHERE ',@referencedBaseTable,'.',@referencedCol,' = ',@checkedTable,'.',@checkedCol,')
            THEN true ELSE false END
            ) WHERE id<>0;');
    
    PREPARE stmt1 FROM @query1;
    EXECUTE stmt1;
    
    IF @proceed = 1
    THEN 
        PREPARE stmt2 FROM @query2;
        EXECUTE stmt2;
        PREPARE stmt3 FROM @query3;
        EXECUTE stmt3;
        SELECT CONCAT(@new_column_name,' column added to table ', @checkedTable,'.') 
            AS 'Message: Done';
        DEALLOCATE PREPARE stmt2;
        DEALLOCATE PREPARE stmt3;
    ELSE 
        SELECT 'All values are present in base table. Adding column is thus unnecessary.' 
            AS 'Message: UNNECESSARY';
    END IF;
    
    DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
;

CALL CheckValueExistsInBaseTable(
    'knownfortitles',
    'titlebasics',
    'titleId',
    'tconst'
    );