Mysql Table Insert If Not Exist Otherwise Update

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



Leave a reply



Submit