Menu
I was trying to update approx 20,000 records in a table by using statements like this one,
however, I got the message say 0 row(s) affected so it didn't work.
UPDATE nc_files SET title ="Worklog details" WHERE "log_name" LIKE "%PC01%"
The log_name field has all the file names with mixed file extensions and cases ,e.g.
PC01.TXT | Worklog details
PC02.txt | Project Summary
PC03.DOC| Worklog details
PC04.doc| Project Summary
The reason why I need to use LIKE is that the updated file only have file name without extension, e.g.
PC01 | Worklog details
PC02 | Project Summary
How do I update records by using LIKE?
907
As an additional suggestion, if your updated file contains file names without extension, I suggest to change your LIKE clause as follows: WHERE log_name LIKE "PC01%" . This will make sure that indexes (if any is present) are used, making queries faster.
The LIKE operator is used in the WHERE clause of the SELECT , UPDATE , and DELETE statements to filter rows based on pattern matching.
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.
Update with condition WHERE clause can be used with SQL UPDATE to add conditions while modifying records. Without using any WHERE clause, the SQL UPDATE command can change all the records for the specific columns of the table.
The log_name is a field name, remove literal quotes from it -
UPDATE nc_files SET title ="Worklog details" WHERE log_name LIKE "%PC01%"
this is because your column name log_name should not be in ' quotes.
"log_name" LIKE "%PC01%" condition will always fail and zero rows will get updated, try this:
UPDATE nc_files
SET title ="Worklog details"
WHERE log_name LIKE "%PC01%";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
