CODEWITHSUNDEEP
javamysqlsqlmariadb
Clive van Hilten
Clive van Hilten

Reputation: 881

MySQL--unknown column in field list

I don't see why the UPDATE below works fine in the console but fails in my Java code

MariaDB [testdb]> DESCRIBE datasift_geo;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| datasift_id | bigint(20) unsigned | NO   | PRI | NULL    |       |
| latitude    | decimal(17,14)      | YES  |     | NULL    |       |
| longitude   | decimal(17,14)      | YES  |     | NULL    |       |
| location    | varchar(100)        | YES  |     | NULL    |       |
| coord       | point               | YES  |     | NULL    |       |
+-------------+---------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

MariaDB [testdb]> SELECT * FROM datasift_geo;
+-------------+-------------------+-------------------+------------+-------+
| datasift_id | latitude          | longitude         | location   | coord |
+-------------+-------------------+-------------------+------------+-------+
|        1601 | 51.45713800000000 | -2.60802800000000 | Bristol UK | NULL  |
+-------------+-------------------+-------------------+------------+-------+

MariaDB [testdb]> UPDATE datasift_geo SET coord = GeomFromText('POINT(51.45713800000000 -2.60802800000000)');
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

================

@Test
public void testGeoCoord() throws SQLException {
    int id = 1601;
    float latitude = 51.45713806152344f;
    float longitude = -2.608027935028076f;
    String sql = "UPDATE `datasift_geo` SET `datasift_geo`.`coord` = GeomFromText('POINT(" + latitude + " " + longitude + ")') WHERE datasift_id = " + id;
    // get database connection, create a PreparedStatement, execute the UPDATE
}

======

Result of executing the UPDATE

java.sql.SQLSyntaxErrorException: Unknown column 'datasift_geo.coord' in 'field list'

Upvotes: 1

Views: 2342

Answers (1)

abhijitcaps
abhijitcaps

Reputation: 594

From the SQL which you have written in the JAVA code, it seems that you have provided tableName.ColumnName, in the SQL. You should only provide the column name.

String sql = "UPDATE `datasift_geo` SET **`datasift_geo`.`coord`** = GeomFromText('POINT(" + latitude + " " + longitude + ")') WHERE datasift_id = " + id;

Syntax is: UPDATE TABLE_NAME SET COLUMN_NAME
Your Java Code has: UPDATE TABLE_NAME SET TABLE_NAME.COLUMN_NAME -- Wrong

Also it can be seen from the Exception

So, your SQL in Jave Code should be as follows:

String sql = "UPDATE `datasift_geo` SET `coord` = GeomFromText('POINT(" + latitude + " " + longitude + ")') WHERE datasift_id = " + id;

Upvotes: 1

Related Questions