MySql Table Insert if not exist otherwise update
Jai is correct that you should use INSERT ... ON DUPLICATE KEY UPDATE
.
Note that you do not need to include datenum in the update clause since it's the unique key, so it should not change. You do need to include all of the other columns from your table. You can use the VALUES()
function to make sure the proper values are used when updating the other columns.
Here is your update re-written using the proper INSERT ... ON DUPLICATE KEY UPDATE
syntax for MySQL:
INSERT INTO AggregatedData (datenum,Timestamp)
VALUES ("734152.979166667","2010-01-14 23:30:00.000")
ON DUPLICATE KEY UPDATE
Timestamp=VALUES(Timestamp)
MySql Table Insert if not exist otherwise update on non-unique columns
You are looking for insert . . . on duplicate key update
. But first, you need a unique index:
create unique index unq_test_userID_name on test(user_ID, name)
Then you can do an insert:
insert into test(user_ID, name, value)
values (36, 'colvis', '3')
on duplicate key update value values(val);
A big caveat here: Storing multiple values in a comma-delimited list is almost never the right thing to do. There are rare exceptions to this rule, which is why I've answered the question.
The right solution is probably a junction table, TestValues
, with one row per "test" and one row per "value". In that case, what you want to do is easy:
insert into TestValues(test_id, value)
values (test_id, val);
Mysql: insert if row doesn't exist, else update.. is there a simpler command?
Try
$user_id = $user_data[0]['user_id'];
$logo_id = $mysqli->real_escape_string($_GET['logo_id']);
$sql = "INSERT INTO results (user_id, logo_id, result_tries)
VALUES ('$user_id', '$logo_id', 0)
ON DUPLICATE KEY UPDATE result_tries = result_tries + 1";
$mysqli->query($sql);
MySQL Insert if not exist, otherwise, return the id
You can do this using concatenation.
$sql = INSERT INTO table (full_name)
SELECT * FROM (SELECT 'John') AS tmp
WHERE NOT EXISTS (
SELECT * FROM table WHERE full_name = 'John'
) LIMIT 1;
$sql .= SELECT id FROM table WHERE full_name = 'John';
MySQL: INSERT or UPDATE if exists, but not based on key column
The most correct way would be to create a unique index on username - course columns and use on duplicate key update.
Obviously, you can issue a select before the insert checking for existing record with same user name and course and issue an insert or an update as appropriate.
MYSQL - insert if it does not exist but update only if it satisfy a criteria
Use the INSERT ... ON DUPLICATE KEY UPDATE
statement like ethis:
INSERT INTO taskStatus(task_id, employee_id, completed_on, task_status) VALUES
(?, ?, ?, ?)
ON DUPLICATE KEY UPDATE
completed_on = VALUES(completed_on),
task_status = CASE WHEN task_status THEN task_status ELSE VALUES(task_status) END;
The CASE
expression will prevent to update task_status
if it is already 1
.
If you also want completed_on
not to be updated if task_status
is 1
, use a similar CASE
expression for it too:
completed_on = CASE WHEN task_status THEN completed_on ELSE VALUES(completed_on) END
Replace the ?
placeholders with the values of the columns that you want to insert.
Create if an entry if it doesn't exist, otherwise update?
Use 'REPLACE INTO':
REPLACE INTO table SET id = 42, foo = 'bar';
See more in the MySQL documentation
Related Topics
Postgres Query to Check a String Is a Number
How to Check If Value Is Inserted Successfully or Not
Select to Get Rows Based on Minimum Value of a Column
Mysql Convert Date from Mm-Dd-Yyyy to Yyyy-Mm-Dd Format
Using T-Sql, Return Nth Delimited Element from a String
Solve Query for Showing Top 5 Selling Products
How to Strip All Non-Alphabetic Characters from String in SQL Server
Postgresql Convert Columns to Rows Transpose
Oracle SQL Get the First and Last Records from an Ordered Dataset
Auto Increment the Column Value by 1 Conditionally in Select Query in SQL
How to Sum Up Time Field in SQL Server
Database Corruption With Mariadb:Table Doesn't Exist in Engine
Mysql: Alter Table If Column Not Exists
Removing Leading Zeroes from a Field in a SQL Statement
Nodejs, MySQL - Json Stringify - Advanced Query
Hive Explode List from Json-String
How to Store the Select Statement Output into Variable Thro Psql ( Postgresql )