CODEWITHSUNDEEP
mysqlsql
Zabs
Zabs

Reputation: 14142

Insert into tbl on Duplicate key update error

I have the following query that I want to amend into an INSERT into tbl if it doesn't exist or UPDATE if it does.

$sql = "UPDATE tbldata temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ? WHERE id = ?";

I am trying the following but have no joy so far - what is wrong with my query below?

$sql = "INSERT INTO tbldata (temperature, temperature_feels, humidity, precipitation) ON DUPLICATE KEY UPDATE id = ?, temperature = ?, temperature_feels = ?, humidity = ?, precipitation = ?";

-- update -- This isn't working... what i am doing wrong?

       $sql = "INSERT INTO weather_data (uv, weather_status, weather_type, temperature, temperature_feels, humidity, precipitation)
                VALUES (?,?,?,?,?,?,?)
                ON DUPLICATE KEY UPDATE
                uv = ?,
                weather_status = ?,
                weather_type = ?,
                temperature = ?,
                temperature_feels = ?,
                humidity = ?,
                precipitation = ?
                UID = ?";

-- update -- my schema

'UID', 'char(17)', 'NO', 'PRI', NULL, ''
'stationID', 'int(11)', 'YES', '', NULL, ''
'temperature', 'float', 'YES', '', NULL, ''
'UV', 'float', 'YES', '', NULL, ''
'temperature_feels', 'float', 'YES', '', NULL, ''
'humidity', 'float', 'YES', '', NULL, ''
'weather_type', 'int(11)', 'YES', '', '-1', ''
'precipitation', 'float', 'YES', '', NULL, ''
'update_station_id', 'tinyint(4)', 'YES', '', '1', ''
'update_due', 'timestamp', 'YES', '', NULL, ''
'weather_status', 'varchar(128)', 'YES', '', NULL, ''

Upvotes: 1

Views: 57

Answers (1)

Vipin Jain
Vipin Jain

Reputation: 3756

You forget write the values here in insert query

$sql = "INSERT INTO tbldata (temperature, temperature_feels, humidity,precipitation)  
VALUES (?,?,?,?) 
ON DUPLICATE KEY UPDATE 
id = ?, 
temperature = ?,
temperature_feels = ?, 
humidity = ?, 
precipitation = ?";

Link for more info http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

Upvotes: 4

Related Questions