This assumes that you have an UNIQUE INDEX or PRIMARY KEY on (pub_id ,paper_code )

INSERT INTO vendor_publication_daily_draws (`alouette.acct`, month, year, paper_code, pub_id, `01`) 
VALUES ('97', '08', '2022', 'STSC', '73609850', '5') 
  ON DUPLICATE KEY UPDATE month=VALUES(month),`alouette.acct` = VALUES(`alouette.acct`)
  ,year = VALUES(year),`01` = VALUES(`01`)

there is also the possibility of a dynamic approach, which i also can provide if abouve fails.

Use INSERT ... ON DUPLICATE KEY UPDATE

QUERY:

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
name="A", age=19

The proper way to do this is using insert ... on duplicate key update. I would write the query as:

INSERT INTO valuation (ticker, depot_id, src_id, valuation_date, value) 
  VALUES ('BK001EUR', 1, 2, '2009-09-09', 14999260.46) 
  ON DUPLICATE KEY UPDATE value = VALUES(value);

(Note the use of VALUES() so you don't have to repeat the input.)

For this to work, you need a unique index on the keys you care about:

create unique index unq_valuation_4 on valuation(ticker, depot_id, src_id, valuation_date);

The duplicate key does not need to be the primary key index.
It can be any unique index.

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)

Use INSERT IGNORE INTO table.

There's also INSERT … ON DUPLICATE KEY UPDATE syntax, and you can find explanations in 13.2.6.2 INSERT ... ON DUPLICATE KEY UPDATE Statement.

Post from bogdan.org.ua according to Google's webcache:

18th October 2007

To start: as of the latest MySQL, syntax presented in the title is not possible. But there are several very easy ways to accomplish what is expected using existing functionality.

There are 3 possible solutions: using INSERT IGNORE, REPLACE, or INSERT … ON DUPLICATE KEY UPDATE.

Imagine we have a table:

CREATE TABLE `transcripts` (
`ensembl_transcript_id` varchar(20) NOT NULL,
`transcript_chrom_start` int(10) unsigned NOT NULL,
`transcript_chrom_end` int(10) unsigned NOT NULL,
PRIMARY KEY (`ensembl_transcript_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Now imagine that we have an automatic pipeline importing transcripts meta-data from Ensembl, and that due to various reasons the pipeline might be broken at any step of execution. Thus, we need to ensure two things:

1. repeated executions of the pipeline will not destroy our > database

2. repeated executions will not die due to ‘duplicate > primary key’ errors.

Method 1: using REPLACE

It’s very simple:

REPLACE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

If the record exists, it will be overwritten; if it does not yet exist, it will be created. However, using this method isn’t efficient for our case: we do not need to overwrite existing records, it’s fine just to skip them.

Method 2: using INSERT IGNORE Also very simple:

INSERT IGNORE INTO `transcripts`
SET `ensembl_transcript_id` = 'ENSORGT00000000001',
`transcript_chrom_start` = 12345,
`transcript_chrom_end` = 12678;

Here, if the ‘ensembl_transcript_id’ is already present in the database, it will be silently skipped (ignored). (To be more precise, here’s a quote from MySQL reference manual: “If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead. For example, without IGNORE, a row that duplicates an existing UNIQUE index or PRIMARY KEY value in the table causes a duplicate-key error and the statement is aborted.”.) If the record doesn’t yet exist, it will be created.

This second method has several potential weaknesses, including non-abortion of the query in case any other problem occurs (see the manual). Thus it should be used if previously tested without the IGNORE keyword.

Method 3: using INSERT … ON DUPLICATE KEY UPDATE:

Third option is to use INSERT … ON DUPLICATE KEY UPDATE syntax, and in the UPDATE part just do nothing do some meaningless (empty) operation, like calculating 0+0 (Geoffray suggests doing the id=id assignment for the MySQL optimization engine to ignore this operation). Advantage of this method is that it only ignores duplicate key events, and still aborts on other errors.

As a final notice: this post was inspired by Xaprb. I’d also advise to consult his other post on writing flexible SQL queries.

Only problem is, you can't use it like a normal query. Control structures like IF or WHILE are only allowed in stored procedures or functions.

Just create a procedure like this:

delimiter $$
create procedure select_or_insert()
begin
  IF EXISTS (select * from users where username = 'something') THEN
    update users set id= 'some' where username = 'something';
  ELSE 
    insert into users (username) values ('something');
  END IF;
end $$
delimiter ;

and call it like this:

call select_or_insert();

and Done

INSERT INTO component_psar (tbl_id, row_nr, col_1, col_2, col_3, col_4, col_5, col_6, unit, add_info, fsar_lock)
VALUES('2', '1', '1', '1', '1', '1', '1', '1', '1', '1', 'N')
ON DUPLICATE KEY UPDATE col_1 = VALUES(col_1), col_2 = VALUES(col_2), col_3 = VALUES(col_3), col_4 = VALUES(col_4), col_5 = VALUES(col_5), col_6 = VALUES(col_6), unit = VALUES(unit), add_info = VALUES(add_info), fsar_lock = VALUES(fsar_lock)

Would work with tbl_id and row_nr having UNIQUE key.

This is the method DocJonas linked to with an example.

1. Create a UNIQUE constraint on your subs_email column, if one does not already exist:

   ALTER TABLE subs ADD UNIQUE (subs_email)
   

2. Use INSERT ... ON DUPLICATE KEY UPDATE:

   INSERT INTO subs
     (subs_name, subs_email, subs_birthday)
   VALUES
     (?, ?, ?)
   ON DUPLICATE KEY UPDATE
     subs_name     = VALUES(subs_name),
     subs_birthday = VALUES(subs_birthday)
   

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT ... ON DUPLICATE KEY UPDATE - dev.mysql.com

3. Note that I have used parameter placeholders in the place of string literals, as one really should be using parameterised statements to defend against SQL injection attacks.

Use INSERT ... ON DUPLICATE KEY UPDATE. For example:

INSERT INTO `usage`
(`thing_id`, `times_used`, `first_time_used`)
VALUES
(4815162342, 1, NOW())
ON DUPLICATE KEY UPDATE
`times_used` = `times_used` + 1