All geek questions in one place

USE MULTIPLE CONDITIONS IN MYSQL

How to use multiple conditions in SQL UPDATE CASES?

COLUMN1 COLUMN2 COLUMN3 BG1 STANDARD FOX1 STANDARD BRB STANDARD NBC STANDARD FOX2 STANDARD CNN3 STANDARD GAM1 STANDARD AI1 STANDARD CQN3 STANDARD GSM1 STANDARD OFD1 STANDARD REST NONSTANDARD WEST NONSTANDARD EAST NONSTANDARD

I want to set column3 = 1, where COLUMN 1 IS FOLLOWED IN ANY FOLLOWING NAMES (BG1, FOX1, BRB, NBC, CNN3, GAM1, AI1) AND WHEN COLUMN2 = STANDARD

and also set column3 = -2, where column1 isnt in the following names ((BG1, FOX1, BRB, NBC, CNN3, GAM1, AI1) and where Column2 = STANDARD.

I wrote these

 UPDATE SET COLUMN3 = 1 WHERE (COLUMN2 = 'STANDARD' AND COLUMN1 = 'BG1' OR 'FOX1'OR 'BRB' OR 'NBC' OR 'CNN3' OR 'GAM1' OR 'AI1') UPDATE SET COLUMN3 = -2 WHERE (COLUMN2 = 'STANDARD' AND COLUMN1 !='BG1' OR 'FOX1'OR 'BRB' OR 'NBC' OR 'CNN3' OR 'GAM1' OR 'AI1')

He does not work. Any tips?

+5
source share
3 answers

Let's look at this first:

 UPDATE SET COLUMN3 = 1 WHERE (COLUMN2 = 'STANDARD' AND COLUMN1 = 'BG1' OR 'FOX1'OR 'BRB' OR 'NBC' OR 'CNN3' OR 'GAM1' OR 'AI1')

In the last part of your WHERE clause, you have OR 'FOX1'OR 'BRB' OR 'NBC' OR 'CNN3' OR 'GAM1' OR 'AI1' This part is always evaluated as False. You do not compare these texts with any column. If you compared them to column1 without brakcets, it would almost always evaluate true, because there are many matches in which the column has one of these values. Check yourself

 SELECT 1 and 0 and 0 or 1 or 0; SELECT 1 and 'aa' or 'bbb'

First, true is always evaluated, and the second is false. This is exactly what happens in your request. Rewrite how

 UPDATE SET COLUMN3 = 1 WHERE (COLUMN2 = 'STANDARD' AND COLUMN1 = 'BG1') AND (column1 = 'FOX1' OR column1 = 'BRB' OR column1 = 'NBC' OR column1 = 'CNN3' OR column1 = 'GAM1' OR column1 = 'AI1')

As an alternative

 UPDATE SET COLUMN3 = 1 WHERE (COLUMN2 = 'STANDARD' AND COLUMN1 = 'BG1') AND column1 = IN ('FOX1','BRB','NBC','CNN3','GAM1','AI1')
0
source

Do you want to use in :

 UPDATE t SET COLUMN3 = 1 WHERE COLUMN2 = 'STANDARD' AND COLUMN1 IN ('BG1', 'FOX1', 'BRB', 'NBC', 'CNN3', 'GAM1', 'AI1') ; UPDATE t SET COLUMN3 = -2 WHERE COLUMN2 = 'STANDARD' AND COLUMN1 NOT IN ('BG1', 'FOX1', 'BRB', 'NBC', 'CNN3', 'GAM1', 'AI1') ;

You can also express this as a single statement using CASE :

 UPDATE t SET COLUMN3 = (CASE WHEN COLUMN1 IN ('BG1', 'FOX1', 'BRB', 'NBC', 'CNN3', 'GAM1', 'AI1') THEN 1 ELSE -2 END) WHERE COLUMN2 = 'STANDARD' ;
0
source

UPDATE TableName SET COLUMN3 = '1'
WHERE COLUMN2 = 'STANDARD' AND column1 IN ('BG1', 'FOX1', 'BRB', 'NBC', 'CNN3', 'GAM1', 'AI1')

UPDATE TableName SET COLUMN3 = '-3'
WHERE COLUMN2 = 'STANDARD' AND column1 NOT IN ('BG1', 'FOX1', 'BRB', 'NBC', 'CNN3', 'GAM1', 'AI1')

0
source

Source: https://habr.com/ru/post/1260174/

More articles:


All Articles