CODEWITHSUNDEEP
mysqlsqldatabaseif-statementsql-update
macourtney7
macourtney7

Reputation: 541

How to update a non-unique column if row exists else update a different row in [MySQL]?

I am trying to construct a query to UPDATE a row if it exists else update a different row. I only wish to update one row each time the query is executed. The second WHERE is for the case when there is no return from the first part of the query.

I wish to update a single attribute that has a default value of 'null'. There isn't a means to use a public key as I've seen in other posts (e.g.).

I can return the desired tuple with a UNION but am then unable to convert the expression to an UPDATE query, as desired:

(SELECT * FROM table
  WHERE (foo BETWEEN x-c AND x+c) AND (bar ..) AND column='null' LIMIT 1)
UNION
(SELECT * FROM table 
  WHERE column='null' LIMIT 1)
LIMIT 1;

This seems to always return the first WHERE if exists else the second WHERE.

I've been receiving syntax errors when trying to design an IF-ELSE based on a count variable:

SET @count=(SELECT COUNT(*) FROM table 
              WHERE (foo BETWEEN x-c AND x+c) AND (bar ..) AND column='null' LIMIT 1);
IF @count THEN
  (UPDATE table SET column='foobar'
  WHERE (foo BETWEEN x-c AND x+c) AND (bar ..) AND column='null' LIMIT 1);
ELSE
  (UPDATE table SET column='foobar'
  WHERE column='null' LIMIT 1);
END IF;

I've also tried to structure a subquery but had no luck in getting the desired behaviour.

I am concerned that using a count could lead to an opportunity for race conditions? I am unsure whether there is a better approach, however.

Any help is much appreciated!

Upvotes: 1

Views: 860

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If I understand correctly, you can just use order by and limit:

update table
    set . . . 
    where column = 'null'
    order by ((foo) AND (bar)) desc
    limit 1;

The desc will put the rows that match the two conditions first. It will then be followed by other rows. The limit chooses the first match.

I think I would be more inclined to order the two conditions separately:

order by (foo) desc, (bar) desc

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562310

Here's one way to do it. Run the first UPDATE. After this runs, the function ROW_COUNT() will return either 0 or 1, depending on whether the first UPDATE affected any rows.

In the second UPDATE, make the change a no-op by setting column=column if ROW_COUNT() indicates that the first UPDATE did change something.

UPDATE table SET column='foobar' WHERE (foo) AND (bar) AND column='null' LIMIT 1;
UPDATE table SET column=IF(ROW_COUNT()=1,column,'foobar') WHERE column='null' LIMIT 1;

However, note that ROW_COUNT() returns 0 if the first UPDATE matched rows, but didn't make any net change, because the rows it found already had the value 'foobar'.

Upvotes: 0

shawnt00
shawnt00

Reputation: 17915

Something like this? I can't promise it's fast:

update <table>
where
    column is null and <foo> and <bar>
    or not (<foo> and <bar>) and not exists (
        select 1 from <table> where column is null and <foo> and <bar>
    );

Upvotes: 0

Related Questions