使用MySql 5.7中的动态列引用从另一个表更新表

Question

我有两个表NEW_TBL和CURRENT_TBL。

CURRENT_TBL中的值需要更新，以便它们等于NEW_TBL。两个表都有一个名为tableId的PK，但NEW_TBL中的列并不总是与CURRENT_TBL匹配。我已经有了使模式相同所需的东西，但我需要一种基于动态列引用更新CURRENT_TBL值的方法。

下面是我如何用javascript实现这一点的一个精心设计的例子。

起始表格：

currentTable = [
['tableId','col2','col3','col4'],
['1','purple','blue','pink'],
['2','purple','red','orange'],
['3','green','yellow','purple']
];

newTable = [
['tableId','col2','col3','col4'],
['1','','blue','pink'],
['2','magenta','blue','pink'],
['4','grey','black','white']
];

功能：

function updateCurrentTable() {
var currentHeader = currentTable[0];
var newHeader = newTable[0];
var currentValues = currentTable.slice(1);
var newValues = newTable.slice(1);
var newIdList = newValues.map(function(val){ // Get list of newTable ids
return val[0];
})
var currentIdList = currentValues.map(function(val){ // Get list of newTable ids
return val[0];
})
for (var newColumn = 0; newColumn<newHeader.length; newColumn++) { // Loop through new table columns
var columnName = newHeader[newColumn];
var newIdColumn = newHeader.indexOf('tableId');
var currentColumnIndex = currentHeader.indexOf(columnName);
var currentIdIndex = currentHeader.indexOf('tableId');
if (columnName !== 'tableId') { // The tableId column is skipped because the id will never be altered
for (var newRow = 0; newRow<newValues.length; newRow++) { // Loop through new table rows
var newId = newValues[newRow][newIdColumn];
var newValue = newValues[newRow][newColumn];
if (currentIdList.indexOf(newId) < 0) { // If tableId exists in newTable but not in currentTable, add the row to currentTable
currentValues.push(newValues[newRow]);
currentIdList.push(newId); // Update the id list
}
for (var curRow =0; curRow < currentValues.length; curRow++) { // Loop through current table rows
var currentId = currentValues[curRow][currentIdIndex];
if (currentColumnIndex !== currentIdIndex) { // The tableId column is skipped because the id will never be altered
if (newIdList.indexOf(currentId) < 0) { // If tableId exists in currentTable but not newTable, remove the row from currentTable
currentValues.splice(curRow,1);
} else if (newId === currentId) { // If the tableIds match, update the value in the current column
currentValues[curRow][currentColumnIndex] = newValue; 
} 
}
}
}
}
}
Logger.log(currentValues)
}

结果：

currentTable = [
['tableId','col2','col3','col4'],
['1','','blue','pink'],
['2','magenta','blue','pink'],
['4','grey','black','white']
];

有没有一种方法可以在SQL中完成上述操作？

谢谢！

Answer 1

我知道您想要匹配列tableId上的表，并用new_tbl中的相应值更新current_tbl中的colHeader。

为此，您可以利用MySQLupdate ... join ... set语法：

update current_tbl c
inner join new_tbl n on c.tableId   = n.tableId
set c.colHeader = n.colHeader

Answer 2

我提出了一个解决方案，可以动态创建SQL语句中比较列的部分(即"currentTable.cost=newTable.cost"(，然后使用列的动态列表运行UPDATE JOIN。

DELIMITER //
CREATE PROCEDURE matchCurrentToNew()
COMMENT 'Update currentTable to match newTable'
BEGIN
DECLARE num_rows INT;
DECLARE col_name TINYTEXT;
DECLARE concatStatement TINYTEXT;
DECLARE concatFields TINYTEXT;
DECLARE done TINYINT DEFAULT FALSE; 
DECLARE col_names 
CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'TASKS'
ORDER BY ordinal_position;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
SET concatStatement = "";
OPEN col_names;
SELECT FOUND_ROWS() into num_rows;
the_loop: LOOP
FETCH NEXT FROM col_names    
INTO col_name; 
IF done THEN
CLOSE col_names;
LEAVE the_loop;
END IF;
IF col_name <> "UniqueId" AND col_name <> "ProjectId" THEN
SET concatFields = CONCAT_WS("=",
CONCAT_WS(".","currentTable",col_name),
CONCAT_WS(".","newTable",col_name)
);
SET concatStatement = CONCAT(concatStatement,",",concatFields);         
END IF;
END LOOP the_loop;
#     Remove comma at the beginning of the concatStatement string
SET concatStatement = TRIM(LEADING "," FROM concatStatement);
SET @sqlText = CONCAT(
'UPDATE currentTable  
JOIN newTable
ON currentTable .UniqueId = newTable.UniqueId 
AND currentTable .ProjectId = newTable.ProjectId
SET ', concatStatement
);
PREPARE stmt FROM @sqlText;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END //
DELIMITER ;