根据另一个表中的值删除表中的列

I have tables like,

我有表像,

Table A

表一个

field1  | field2 | field3 | field4 | field5

Table B

表B

id    | field_names
--------------------
1       field2
2       field3

I have to delete field2 and field3 from Table A ( means field names in Table B ).

我必须从表A中删除field2和field3(表示表B中的字段名)。

Is there any way to drop field names which present in Table B. I can't delete the fields manually.

表b中有删除字段名的方法吗?我无法手动删除字段。

Thanks in advance

谢谢提前

3 个解决方案

#1

Not sure why you need this. anyway MySQL has no such feature. but if you want really do that, execute following query and execute each rows.

不知道为什么需要这个。总之，MySQL没有这样的功能。但是如果您真的想这样做，请执行以下查询并执行每一行。

SELECT CONCAT('ALTER TABLE TableA DROP ', field_names, ';') FROM TableB;

for examples with PHP, (all error check is omitted)

对于PHP示例，(省略所有错误检查)

$query = "SELECT CONCAT('ALTER TABLE TableA DROP ', field_names, ';') AS stmt FROM TableB;";

$result = mysqli_query($query);

while ($row = mysqli_fetch_assoc($result))
{
    mysqli_query($rows['stmt']);
}

#2

This would help:

这将有助于:

SELECT @s := CONCAT('ALTER TABLE tableA DROP COLUMN ', GROUP_CONCAT(field_names SEPARATOR ', DROP COLUMN '), ';') FROM tableB;
PREPARE stmt FROM @s;
EXECUTE @s;

#3

First create a procedure:

首先创建一个程序:

DELIMITER //
CREATE PROCEDURE deleteTableAColumns()
BEGIN
    DECLARE columnToDelete VARCHAR(16);
    DECLARE done INT DEFAULT FALSE;
    DECLARE columnsToDeleteCur CURSOR FOR SELECT field_names FROM tableB;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN columnsToDeleteCur;
    read_loop: LOOP 
        FETCH columnsToDeleteCur INTO columnToDelete;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET @dropColumnQuery = CONCAT('ALTER TABLE tableA DROP ', columnToDelete, ';');
        PREPARE dropColumnQueryStatement FROM @dropColumnQuery;
        EXECUTE dropColumnQueryStatement;
        DEALLOCATE PREPARE dropColumnQueryStatement; 
    END LOOP;
    CLOSE columnsToDeleteCur;
END
//
DELIMITER ;

Then call the procedure whenever you want to execute it:

然后在您想执行的时候调用该过程:

CALL deleteTableAColumns();

#1