Tag Info

Hot answers tagged

7

First here is sample data mysql> drop table if exists mytable; Query OK, 0 rows affected (0.03 sec) mysql> create table mytable -> ( -> id int not null, -> value VARCHAR(255), -> primary key (id) -> ); Query OK, 0 rows affected (0.06 sec) mysql> insert into mytable (id) values (1),(2),(3); Query OK, ...


3

Assuming that you don't want to load the data from the CSV file into a database table and then do a correlated UPDATE, UPDATE mytable t SET value = (SELECT value FROM tbl_with_csv_data csv WHERE csv.primary_key = t.primary_key) WHERE EXISTS( SELECT 1 FROM tbl_with_csv_data csv WHERE ...


3

You need to use the information_schema database to generate the script. Collect all columns from every table of every database that have the following criteria: Exclude the following databases: information_schema performance_schema mysql COLUMN_TYPE values with one of the following characteristics: starts with CHAR( starts with VARCHAR( ends with TEXT ...


3

SELECT SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) ; If you want update the strings from a table, chopping off what is on the right of .com: UPDATE tableX SET col = SUBSTRING(col, 1, CHAR_LENGTH(col) - CHAR_LENGTH( SUBSTRING_INDEX(col, '.com', -1) ) ) WHERE SUBSTRING_INDEX(col, '.com', -1) ...


3

As you found, you have to use '' to represent a single quote ('). You do not need to use the SPACE() function: select replace(character_column, '''', '') from your_table Note: Using SPACE() could actually create an issue as it returns VARCHAR(4000).


2

This may come across as silly, but if the CMS ignores subsequent <!--more--> tags, you shouldn't really care if it adds one after every </em> closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it ...


1

Disable any FKs pointing to the table Create table SAVE as SELECT * FROM A Truncate table A Begin to create a script with the following with xfer as (select cast ('a' as text) as targ_tab, cast ('b' as text) as from_tab) select xfer.*, tabs.table_name as targ_table_name, cols.table_name as from_table_name, 'alter table ' || tabs.table_name || ' add (' ...


1

Use the REPLACE function, e.g. select replace(yourfield,'''','') from yourtable where ... Here we are replacing a single quote character with an empty string. Since SQL character literals are themselves enclosed in single quotes, we escape the single quote character in the literal by doubling it. More information about the use of the REPLACE function ...


1

Look carefully at the query's output Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 The expression Rows matched is based on WHERE bug_id=452;. That matched. When you executed it, the change evidently failed because the short_desc column didn't change because short_desc does not equal 'I don\'t know why my previous comment ...


1

Perhaps this UPDATE may do it: UPDATE tablename SET `time` = `time` - INTERVAL 2 YEAR;


1

Here is the output of the NOW() function mysql> select now(); +---------------------+ | now() | +---------------------+ | 2012-04-24 22:07:47 | +---------------------+ 1 row in set (0.03 sec) mysql> This is the format to enter mydate. For a double, just append .0 to any integer. No need for numbers to to have quotes: REPLACE into ...



Only top voted, non community-wiki answers of a minimum length are eligible