1

Topic: Help to optimize request UPDATE

Prompt how correctly to make one request instead?
UPDATE table SET column ='test ' WHERE column_field ='field_1 ';
UPDATE table SET column ='hello ' WHERE column_field ='field_2 ';
UPDATE table SET column ='world ' WHERE column_field ='field_3 ';
Value in a column column should change depending on value in a column column_field

2

Re: Help to optimize request UPDATE

Obviously, it is necessary to describe this dependence function or the table.

3

Re: Help to optimize request UPDATE

If the field column_field has high selectivity and on it there is an index it is better to leave as is.

4

Re: Help to optimize request UPDATE

Appologies wrote:

Prompt how correctly to make one request instead?
UPDATE table SET column ='test ' WHERE column_field ='field_1 ';
UPDATE table SET column ='hello ' WHERE column_field ='field_2 ';
UPDATE table SET column ='world ' WHERE column_field ='field_3 ';
Value in a column column should change depending on value in a column column_field

UPDATE table SET col = (CASE column_field
WHEN ' field1 ' THEN ' test'
WHEN ' field2 ' THEN ' hello'
WHEN ' field3 ' THEN ' world'
END)
WHERE column_field IN (' field1 ', ' field2 ', ' field3 '); - without it to all remaining distributes NULL

5

Re: Help to optimize request UPDATE

tip78 wrote:

it is passed...

UPDATE table SET col = (CASE column_field
WHEN ' field1 ' THEN ' test'
WHEN ' field2 ' THEN ' hello'
WHEN ' field3 ' THEN ' world'
END)
WHERE column_field IN (' field1 ', ' field2 ', ' field3 '); - without it to all remaining distributes NULL

That that is necessary, and if on an example it is necessary for me to put value (field_1/field_2/field_3) at once in 2 different columns, that is to double, to use something similar to it?

UPDATE table SET col = (CASE column_field
WHEN ' field1 ' THEN ' test'
WHEN ' field2 ' THEN ' hello'
WHEN ' field3 ' THEN ' world'
END),
col2 = (CASE column_field
WHEN ' field1 ' THEN ' test'
WHEN ' field2 ' THEN ' hello'
WHEN ' field3 ' THEN ' world'
END)
WHERE column_field IN (' field1 ', ' field2 ', ' field3 '); - without it to all remaining distributes NULL

Or it is possible in request as that to make easier duplication of values at once in 2 columns (values will be identical)

6

Re: Help to optimize request UPDATE

In general, yes, installed values for each field need to be enumerated.
Another matter, whence them to take. At a small amount variants can be enumerated explicitly in request. Actually, as in an example from the first post - three variants and one field for update.
At usage of identical changeovers in two and more places it is possible to make the user function which will return values according to argument and to cause it repeatedly.
Or, to enumerate correspondences in the additional table, like the reference manual, we name it ` dop `:
arg | result
field1 | test
field2 | hello
field3 | word
Then it is possible to take correspondences so approximately:

UPDATE
` table `, ` dop`
SET
` table `. ` col1 ` = ` dop `. ` result `;
` table `. ` col2 ` = ` dop `. ` result `;
` table `. ` col3 ` = ` dop `. ` result`
WHERE
` table `. ` column_field ` = ` dop `. ` arg`

7

Re: Help to optimize request UPDATE

Appologies wrote:

That that is necessary, and if on an example it is necessary for me to put value (field_1/field_2/field_3) at once in 2 different columns, that is to double, to use something similar to it?

Try col2 = col
But it is not exact

8

Re: Help to optimize request UPDATE

tip78 wrote:

try col2 = col
But it is not exact

the Second request - precisely.

9

Re: Help to optimize request UPDATE

Under the total it turns out here that. In general the task costs what to install value in two fields on the basis of values from the third. But, if this coincidence it is not found, it is necessary to install certain number instead of NULL. Such request will be correct or it is a crutch?
[sql] - we Install value in 1 column starting with 3 columns
UPDATE xf_user SET user_group_id = (CASE faction
WHEN ' LSPD ' THEN ' 27'
WHEN ' SFPD ' THEN ' 28'
WHEN ' FBI ' THEN ' 29'
WHEN ' Area 51 Army ' THEN ' 32'
WHEN ' Grove Street Gang ' THEN ' 34'
WHEN ' Emergency Medical Services ' THEN ' 31'
WHEN ' Los Lantos Vagos Gang ' THEN ' 35'
WHEN ' LS News ' THEN ' 30'
WHEN ' The Ballas Gang ' THEN ' 33'
WHEN ' The Rifa Gang ' THEN ' 37'
WHEN ' Varrios Los Aztecas ' THEN ' 36'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' streets ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' pizzas ' THEN ' 2'
WHEN faction =' the dealer ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' the bus ' THEN ' 2'
WHEN faction =' products ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' a loader ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' roads ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
END)
WHERE faction IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services ','Los Lantos Vagos Gang ', ' LS News','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We double value from 1 column in the second column
UPDATE xf_user SET user_group_id = display_style_group_id
WHERE faction IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services ','Los Lantos Vagos Gang ', ' LS News','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We install value "2" in 1 column if in 3 it is not found certain coincidence
UPDATE xf_user SET user_group_id = ' 2'
WHERE faction NOT IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services','Los Lantos Vagos Gang ', ' LS News ','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We double value "2" in 2 column if in 3 it is not found certain coincidence
UPDATE xf_user SET user_group_id = display_style_group_id
WHERE faction NOT IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services','Los Lantos Vagos Gang ', ' LS News ','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas'); [/sql]

10

Re: Help to optimize request UPDATE

I am sorry for . I can not edit the previous message.
Under the total it turns out here that. In general the task costs what to install value in two fields on the basis of values from the third. But, if this coincidence it is not found, it is necessary to install certain number instead of NULL. Such request will be correct or it is a crutch?

-- We install value in 1 column starting with 3 columns
UPDATE xf_user SET user_group_id = (CASE faction
WHEN ' LSPD ' THEN ' 27'
WHEN ' SFPD ' THEN ' 28'
WHEN ' FBI ' THEN ' 29'
WHEN ' Area 51 Army ' THEN ' 32'
WHEN ' Grove Street Gang ' THEN ' 34'
WHEN ' Emergency Medical Services ' THEN ' 31'
WHEN ' Los Lantos Vagos Gang ' THEN ' 35'
WHEN ' LS News ' THEN ' 30'
WHEN ' The Ballas Gang ' THEN ' 33'
WHEN ' The Rifa Gang ' THEN ' 37'
WHEN ' Varrios Los Aztecas ' THEN ' 36'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' streets ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' pizzas ' THEN ' 2'
WHEN faction =' the dealer ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' the bus ' THEN ' 2'
WHEN faction =' products ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' a loader ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' roads ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
WHEN faction =' ' THEN ' 2'
END)
WHERE faction IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services ','Los Lantos Vagos Gang ', ' LS News','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We double value from 1 column in the second column
UPDATE xf_user SET user_group_id = display_style_group_id
WHERE faction IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services ','Los Lantos Vagos Gang ', ' LS News','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We install value "2" in 1 column if in 3 it is not found certain coincidence
UPDATE xf_user SET user_group_id = ' 2'
WHERE faction NOT IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services','Los Lantos Vagos Gang ', ' LS News ','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');
-- We double value "2" in 2 column if in 3 it is not found certain coincidence
UPDATE xf_user SET user_group_id = display_style_group_id
WHERE faction NOT IN (' LSPD ', ' SFPD ', ' FBI ', ' Area 51 Army ', ' Grove Street Gang ', ' Emergency Medical Services','Los Lantos Vagos Gang ', ' LS News ','The Ballas Gang ', ' The Rifa Gang ', ' Varrios Los Aztecas');

11

Re: Help to optimize request UPDATE

Appologies wrote:

if this coincidence it is not found, it is necessary to install certain number instead of NULL. Such request will be correct or it is a crutch?

Any crutches. Is ELSE for the job  values, it is regular functional CASE. Glance in dock https://dev.mysql.com/doc/refman/5.7/en … rator_case

12

Re: Help to optimize request UPDATE

This all game in the table push and then:

UPDATE xf_user SET user_group_id = (SELECT id FROM ` game ` WHERE name = ' a word ');
display_style_group_id = user_group_id
WHERE faction IN (SELECT name FROM ` game `)

To steam of remarks:
It is more ID-shnikov, in a word ass;
There is a sense on groups to divide, instead of listing of all list