How To Update String Values In MySQL Tables

To replace strings across one or more columns in MySQL tables, use the REPLACE() function with an UPDATE statement. This is useful when migrating domains, fixing URLs, updating API endpoints, or correcting data patterns at scale.

Basic Syntax

UPDATE table_name
SET column_name = REPLACE(column_name, 'old_string', 'new_string')
WHERE column_name LIKE '%old_string%';

The WHERE clause isn’t required, but including it significantly improves performance by limiting the scan to rows that actually contain the target string.

Practical Examples

Replace a domain in a URL column:

UPDATE users
SET website_url = REPLACE(website_url, 'domain.com', 'www.domain.com')
WHERE website_url LIKE '%domain.com%';

Update multiple columns at once:

UPDATE posts
SET content = REPLACE(content, 'old_api.com', 'new_api.com'),
    author_url = REPLACE(author_url, 'old_api.com', 'new_api.com')
WHERE content LIKE '%old_api.com%'
   OR author_url LIKE '%old_api.com%';

Replace in a large table safely with LIMIT:

For massive tables, process replacements in batches to avoid locking issues:

UPDATE large_table
SET data_column = REPLACE(data_column, 'old', 'new')
WHERE data_column LIKE '%old%'
LIMIT 10000;

Run this query repeatedly until no rows are affected.

Case-insensitive replacement:

MySQL’s REPLACE() is case-sensitive by default. For case-insensitive matching, combine with BINARY or use COLLATE:

UPDATE pages
SET content = REPLACE(content, 'HTTP://', 'https://')
WHERE content COLLATE utf8mb4_general_ci LIKE '%HTTP://%';

Important Considerations

Before running any UPDATE, back up your data:

mysqldump -u username -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

Test with SELECT first:

Always preview what will change before executing the update:

SELECT id, column_name, REPLACE(column_name, 'old', 'new') as preview
FROM table_name
WHERE column_name LIKE '%old%'
LIMIT 10;

Check row count:

SELECT COUNT(*) FROM table_name
WHERE column_name LIKE '%old%';

Performance Notes

Add indexes on columns being searched. For the LIKE clause to use an index efficiently, avoid leading wildcards.
The WHERE LIKE condition is crucial for large tables — scanning an entire table with millions of rows is expensive.
For very large tables with frequent updates, consider scheduled batch jobs during off-peak hours.
Monitor the slow query log if replacements take longer than expected:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Alternative: Using Subqueries

For conditional replacements or complex logic:

UPDATE products
SET description = REPLACE(description, 'Discontinued', 'Out of Stock')
WHERE id IN (SELECT id FROM inventory WHERE status = 'inactive');

JSON/Structured Data

If you’re working with JSON columns in MySQL 5.7+, REPLACE() still works on the entire JSON string, but use JSON_SET() or JSON_REPLACE() for targeted updates within JSON objects:

UPDATE config
SET settings = JSON_SET(settings, '$.api_url', 'https://new-api.com')
WHERE JSON_EXTRACT(settings, '$.api_url') = 'https://old-api.com';

Always test replacements in a staging environment first, especially with production data.

2026 Best Practices and Advanced Techniques

For How to Update String Values in MySQL Tables, understanding both the fundamentals and modern practices ensures you can work efficiently and avoid common pitfalls. This guide extends the core article with practical advice for 2026 workflows.

Troubleshooting and Debugging

When issues arise, a systematic approach saves time. Start by checking logs for error messages or warnings. Test individual components in isolation before integrating them. Use verbose modes and debug flags to gather more information when standard output is not enough to diagnose the problem.

Performance Optimization

Monitor system resources to identify bottlenecks
Use caching strategies to reduce redundant computation
Keep software updated for security patches and performance improvements
Profile code before applying optimizations
Use connection pooling and keep-alive for network operations

Security Considerations

Security should be built into workflows from the start. Use strong authentication methods, encrypt sensitive data in transit, and follow the principle of least privilege for access controls. Regular security audits and penetration testing help maintain system integrity.

Related Tools and Commands

These complementary tools expand your capabilities:

Monitoring: top, htop, iotop, vmstat for system resources
Networking: ping, traceroute, ss, tcpdump for connectivity
Files: find, locate, fd for searching; rsync for syncing
Logs: journalctl, dmesg, tail -f for real-time monitoring
Testing: curl for HTTP requests, nc for ports, openssl for crypto

Integration with Modern Workflows

Consider automation and containerization for consistency across environments. Infrastructure as code tools enable reproducible deployments. CI/CD pipelines automate testing and deployment, reducing human error and speeding up delivery cycles.

Quick Reference

This extended guide covers the topic beyond the original article scope. For specialized needs, refer to official documentation or community resources. Practice in test environments before production deployment.