Issue with creating insert select query in mySQL

Question

I have the following mySQL query that I would like to execute inside a PHP script:

$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" ;

The error I get is:

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

This happens to be one of those errors where you can read the error message carefully and find your way to your answer. With respect, give it a try. — O. Jones
– O. Jones, Commented Oct 11, 2019 at 11:43
try add database name to your query,like database.InsertingTable1 — Rajith Thennakoon
– Rajith Thennakoon, Commented Oct 11, 2019 at 11:43
@RajithThennakoon That won't really solve the error, and the problem is with a column and not the table — Carl Binalla
– Carl Binalla, Commented Oct 11, 2019 at 11:44
I’m not entirely sure what it means by ambiguous in the field list, am I repeating the name more than I should — John
– John, Commented Oct 11, 2019 at 11:46
provide column name device in select statement with respected table name like Table1.device or CheckingTable1.device — Divyesh patel
– Divyesh patel, Commented Oct 11, 2019 at 11:46

nbk · Accepted Answer · 2019-10-11 11:46:42Z

1

use this Select statement If you want to show the device column if the first Table named Table1.

Mysql tells you in the error message that both tabkles have such a column namede 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" ;

answered Oct 11, 2019 at 11:46

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GMB · Accepted Answer · 2019-10-11 11:46:19Z

The error arises because both tables have a device column. As a consequence, when you SELECT device or ORDER BY device, your RDBMS cannot assess which one it should use.

You would argue that, since you are joining on the device column across tables, the value of both columns is guaranteed to be the same; this is true in this specific case, but your RDMBS does not figure that by itself.

You need to prefix the column with the table it comes from. Also, it is a good practice to use table aliases, to make the query shorter. I added the prefix to the ambiguous column but I suggest that you do prefix all columns; this makes the query easier to understand.

INSERT INTO InsertingTable1 (place, device, description, amount) 
SELECT place, t.device, description, amount
FROM Table1 t
INNER JOIN CheckingTable1 c ON t.device = c.device 
ORDER BY place, t.device

NB: ASC is the default sorting order, hence you may not specifiy it.

Collectives™ on Stack Overflow

Issue with creating insert select query in mySQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related