Multiple Updates for Multiple String Values Using CASE WHEN and LIKE?

Related searches

I am attempting to update several values in a table based on several conditions. If a column (let's call it "name") contains some set of strings, I would like to change the name to something else. For example, if name contains two consecutive a's, like "aa", I would like to change it to just "A".

Here is my query that is currently not working:

UPDATE table
   SET name = CASE name 
                      WHEN name like "%aa%" then "A" 
                      WHEN name like "%er%" then "E"
                      ELSE "Z"
                      END
 WHERE name is not null

I know it is a non-sensical example, but I'm not great with coming up with those on the spot. The error I am currently getting is:

"No matching signature for operator CASE for argument types: STRING, BOOL, STRING, BOOL, STRING, STRING at [2:18].

Any thoughts?

Remove name from CASE name and replace double quotes " with single quotes ':

UPDATE table
   SET name = CASE 
                      WHEN name like '%aa%' then 'A' 
                      WHEN name like '%er%' then 'E'
                      ELSE 'Z'
                      END
 WHERE name is not null

P.S. in this example and your question query the word table is the name of the table not the keyword table. That means that in some databases you will have problems and you will have to put this word in quotes.

In this DEMO you can see that the query from my answer will work in MySQL database when you put word table in quotes.

In this DEMO you can see that the query from my answer will work in Oracle database when you put word table in double quotes. Also you will see that the strings(values of columns) can not be between double quotes in Oracle.

Note: Some of the databases will not cause errors if the string(column value) is between double quotes but it is better to use single quotes for this and because you have not tagged a database that you use this are some guidelines that will help you.

Case with multiple conditions, ,CASE WHEN i.DocValue ='F2' AND c.CondCode IN ('ZPR0','ZT10','Z305') THEN c.CondVal ELSE 0 END as Value. There are two types of CASE statement,� Single quotes ' ' are used to enclose text values. For example, I assume that Welcome Support CEL is a field name, so you should use [Welcome Support CEL]. If Welcome Support is a literal text value, the first update statement becomes. strSQL = "UPDATE Tbl_CEL SET [Welcome Support CEL] = 'Welcome Support'"

Remove the first name in the CASE clause:

UPDATE table
   SET name = CASE WHEN name like "%aa%" then "A" 
                   WHEN name like "%er%" then "E"
                   ELSE "Z"
                   END
 WHERE name is not null

Solved: Case Statement Like With Multiple Values, Since the full urls vary, but with a few strings that are consistently embedded within it, I want to catch them all by asking for like '%string%'. By Allen G. Taylor . You can use a more compact form of the SQL CASE expression if you’re comparing a test value for equality with a series of other values. This form is useful within a SELECT or UPDATE statement if a table contains a limited number of values in a column and you want to associate a corresponding result value to each of those column values.

Exactly what The Impaler said,remove the first name in the CASE clause. SQL syntax does not call for the column name directly after case. For example (From W3 schools):

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

https://www.w3schools.com/sql/sql_case.asp

Good luck!

SQL CASE, many WHEN / THEN statements as you'd like: So if the value in the weight column of a given You can also string together multiple conditional statements with AND� I've found plenty of info around about updating multiple rows with the same value using "WHERE columname IN", and I've got that down. But, I'm needing to UPDATE a column in multiple rows with a

Remove the first instance of the word name

UPDATE table
   SET name = CASE 
                      WHEN name like "%aa%" then "A" 
                      WHEN name like "%er%" then "E"
                      ELSE "Z"
                      END
 WHERE name is not null

There are two ways to use case:

1) CASE WHEN name=1 THEN WHEN name=2 THEN...

2) CASE name WHEN 1 THEN WHEN 2 THEN...

In the first case it looks for the first TRUE expression, in the second for the first expression that is equal to name. You made a mix of the two, but for the interpreter it is the second form. Therefore, it was looking for the first expression equal to name. When it reads the first WHEN, it checks whether name is equal to the argument (name like "%aa%") But (name like "%aa%") is a boolean, name is a string, hence the error.

Understanding the SQL Server CASE statement, Master the Case statement in SQL Server the quick way with this where you want to set the value of one column depending upon the values in The SQL Server CASE Statement consists of at least one pair of WHEN and THEN statements. You can evaluate multiple conditions in the CASE statement. I have SQL server Table in which there is column that I wanted to update according to a 2 columns value that are present in current row. In this scenario, we can use CASE expression. CASE expression is used for selecting or setting a new value from input values.

Using CASE to Add Logic to a SELECT, When you need to add IF-THEN logic to a SELECT statement, CASE is here to help. As you write an SQL query, you may need to get values from multiple and DELETE statements or in WHERE , IN , LIKE , ORDER BY , and For animals whose weight is less than 100 kilograms, the small string will be� If 2 or more sub-strings are all being replaced by the same new value, then you can use REGEXP_REPLACE to do all of them in a single function call. Regular expressions can also be used when there is a common pattern among the sub-strings to be replaced (e.g., replacing <TAG>F00999</TAG> with '<TAG>FUBAR999</TAG>, where TAG can be any element

Column values on multiple rows can be updated in a single UPDATE statement if the condition specified in WHERE clause matches multiple rows. In this case, the SET clause will be applied to all the matched rows.

We can use the CASE statement to update multiple columns in a table, even using separate update criteria for each column. This example updates the publishers table to set the state column to "--" for non-USA companies, and changes the city for one particular publisher, all in one table read operation.

Comments
  • Tag your question with the database you are using. Sample data and desired results would help. The antecedent of "it" in the last sentence of the first paragraph is unclear.
  • Hi @AaronSpencerPhalin I see you are a member of SO for some time now and from your question history I see this small article could be of use: stackoverflow.com/help/someone-answers Cheers!
  • Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database. Stick to using single quotes.