php - 在 mySQL 中创建插入选择查询的问题

Question

我想在 PHP 脚本中执行以下 mySQL 查询:

$sql = "INSERT INTO InsertingTable1 (place, device, description, amount) SELECT `place`, `device`,`description`,`amount` 
    FROM Table1 INNER JOIN CheckingTable1
    ON Table1.device = CheckingTable1.device 
    ORDER BY place ASC, device ASC" ;

我得到的错误是:

Fatal error: Uncaught exception 'mysqli_sql_exception' with message 'Column 'Device' in field list is ambiguous'

Answer 1

使用这个 Select 语句如果要显示设备列，如果第一个表名为 Table1。

Mysql在报错信息中告诉你，两个tabkles都有这么一个列named devices

$sql = "INSERT INTO InsertingTable1 (place, device, description, amount) SELECT `place`, Table1.`device`,`description`,`amount` 
    FROM Table1 INNER JOIN CheckingTable1
    ON Table1.device = CheckingTable1.device 
    ORDER BY place ASC, device ASC" ;