CODEWITHSUNDEEP
mysql
Nathan
Nathan

Reputation: 360

MySql WHERE condition based on unique index

I have the following table groupToScore:

CREATE TABLE `groupToScore` (
  `groupId` int NOT NULL,
  `scoreId` varchar(255) NOT NULL,
  `scoreName` varchar(255) DEFAULT NULL,
  UNIQUE KEY `gToS` (`groupId`,`scoreId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

The pair (groupId, scoreId) form a unique key gTos for this table.

My question is how do I perform UPDATE using gTos in the WHERE clause? It would look something like, UPDATE groupToScore SET scoreName = #{scoreName} WHERE gToS is equal to (groupId, scoreId).

Upvotes: 0

Views: 86

Answers (4)

Rick James
Rick James

Reputation: 142238

groupId = 1 and scoreId = 1

will fail to fully use your

UNIQUE KEY `gToS` (`groupId`,`scoreId`)

because of a type conflict.

When comparing a varchar column to an integer literal, the column is converted to an integer before performing the test. Do, instead

groupId = 1 and scoreId = "1"

It does not matter if you have groupId = "1"; the string will be converted to a number and the index can still be used.

The update is simply

UPDATE groupToScore SET
    scoreName = #{scoreName}
    WHERE groupId = 123, scoreId = "987"

And/or, change the datatype of `scoreId to be something numeric. (Of course, this assumes its values are really numbers.

Upvotes: 0

blabla_bingo
blabla_bingo

Reputation: 2152

You cannot use the key name directly but you can use its expression. Try this:

UPDATE groupToScore SET scoreName = #{scoreName} WHERE (`groupId`,`scoreId`) = (groupId, scoreId)
--e.g for a single match
insert groupToScore values(1,3,'dd'),(1,2,'xx');
UPDATE groupToScore SET scoreName = 'aa' WHERE (`groupId`,`scoreId`) = (1, 2);

--e.g for multiple matches
UPDATE groupToScore SET scoreName = 'kk' WHERE (`groupId`,`scoreId`) in (
(1,2),
(1,3)
);

Upvotes: 1

Amadan
Amadan

Reputation: 198324

You can't use indices directly. The SQL engine uses the index automatically if it is applicable. Therefore, you simply query for WHERE groupId = ? AND scoreId = ?.

Upvotes: 1

Hi computer
Hi computer

Reputation: 1121

UPDATE groupToScore SET scoreName = #{scoreName} WHERE groupId = 1 and scoreId = 1;

I believe mysql server will choose a proper index for you and you can get the one sql indexing information by put a explain at the top of the sql

explain UPDATE groupToScore SET scoreName = #{scoreName} WHERE groupId = 1 and scoreId = 1;

Upvotes: 1

Related Questions