TIME NOW
World current time now,
CALENDAR
Calendar monthly, yearly
login CONVERT LENGTH
login CONVERT TEMPERATURE
login DICTIONARIES, LISTS
login SCIENCE EDUCATION RELIGION
login WORK CALCULATOR
login CALCULATE LIFE

MYSQL dabatase. How to, tutorial, examples

Previous articlePage bottomNext article  ALL TOPICS

Mysql. update insert query example: replace

 
UPDATE QUERY
UPDATE t_songs set `author` = REPLACE( author , '\n', '')

UPDATE t_songs set `author` = REPLACE( author , '\n\r', '')

UPDATE t_songs set `author` = REPLACE( author , '\r', '')

UPDATE t_songs set `author` = REPLACE( author , '"', '')

UPDATE `t_songs_authors` SET `name` = CONCAT( "__", `name` )

UPDATE `t_songs_authors` SET `name` = trim( `name` )

UPDATE t_songs_tmp set `text` = REPLACE( `text` , '\n\n\n', '\n')

UPDATE t_songs_tmp set `text` = REPLACE( `text` , '\r', '')

UPDATE t_tostai set `text` = REPLACE( `text` , ';?', ';')

UPDATE t_tostai set `text` = REPLACE( `text` , '"', '')

UPDATE `t_songs` set author = trim(`author`)

UPDATE `t_songs` set name= trim(`name`)
INSERT
INSERT IGNORE INTO  `t_songs_authors` (  `name` )
SELECT DISTINCT  `author`
FROM  `t_songs_tmp`
WHERE 1

INSERT INTO t_songs_authors( name )
SELECT DISTINCT author
FROM t_songs

INSERT IGNORE INTO  `t_songs` (  `name`, `author`, `text` )
SELECT  `name`, `author`, `text`
FROM  `t_songs_tmp`
WHERE 1
PRIMARY KEY VALIDATION
Insertion into table with id primary key:
INSERT INTO `testas`.`t_testas`
(`id`, `vardas`, `gdata`, `pinigai`)
VALUES
(NULL, 'jonas', '2011-03-15', '1000'),
(NULL, 'petras', '2011-03-24', '5000');

Primary key validation
INSERT INTO `testas`.`t_testas`
(`id`, `vardas`, `gdata`, `pinigai`)
VALUES
(1, 'simas', '2011-03-15', '1000')

Errot: Duplicate entry '1' for key 'PRIMARY'

Good:
INSERT INTO `testas`.`t_testas`
(`id`, `vardas`, `gdata`, `pinigai`)
VALUES
(3, 'simas', '2011-03-15', '1000')

1 row(s) inserted.
 
UPDATE `testas`.`t_testas`
SET `pinigai` = '2000'
WHERE `t_testas`.`id` =1;

UPDATE `testas`.`t_testas`
SET `pinigai` = '2000'

UPDATE `testas`.`t_testas`
SET `pinigai` = `pinigai` * 2

UPDATE `testas`.`t_testas`
SET `vardas` = 'jonas1',
`pinigai` = '5000'
WHERE `t_testas`.`id` =1;
 
Update query according sort order results:
 SET @x = 0;
UPDATE `t_world_countryinfo` SET population_order = (@x:[email protected]+1) ORDER BY population desc;

UPDATE, SELECT SUM or COUNT form another table:
UPDATE `t_world_countryinfo` c
INNER JOIN
(
   SELECT `name`, sum(`range`) AS `sum`
   FROM `t_world_ip_addresses`
   GROUP BY `name`
) p ON c.`iso_alpha2` = p.`name`

SET c.`ip_count` = p.`sum`

Another example:
Before updating we check
SELECT c.*, p.*  from `t_world_countryinfo` c
INNER JOIN
(
   SELECT `country_code`, count(`id_r`) AS `count`
   FROM `t_world_posts`
   GROUP BY `country_code`
) p ON c.`iso_alpha2` = p.`country_code`


The query is run only after checking and analyzing results:
UPDATE `t_world_countryinfo` c SET c.`has_posts` = 0

UPDATE  `t_world_countryinfo` c
INNER JOIN
(
   SELECT `country_code`, 1 AS `count`
   FROM `t_world_posts`
   GROUP BY `country_code`
) p ON c.`iso_alpha2` = p.`country_code`
SET c.`has_posts` = p.`count`

Delete dublicates, after that update from one table to another:
DELETE t2
FROM   tmp  t1
JOIN   tmp  t2 ON (t2.a = t1.a ) AND t2.id > t1.id; 

UPDATE `t_world_countryinfo` c
INNER JOIN
(
   SELECT `a`, `b`
   FROM `tmp`
   
) p ON c.`currency` = p.`a`

SET c.`currencyNameLong` = p.`b`;
 
 
Previous articlePage topNext article  ALL TOPICS



 Use username: Guest, Anonymous, Programmer






QUOTES:
I was really happy with Alana Morris' pitching.
David Dachs
Life is not accountable to us. We are accountable to life.
Denis Waitley
The opposite of love is not hate, the opposite of love is ignorance.
Brian Hwang