updated 2019-04-19
My notes and sample code from experimenting and cleaning-up data.
Always run a select statement before running an update or delete.
Note: The phpMyAdmin tool, which is very valuable, adds “limit 0,30” to the end of most SELECT statements so don’t be tricked into thinking you have been shown all the selected records. You can change it by setting the config variable $cfg['MaxRows'] to a larger number in the congig.inc.php file in phpmyadmin.
SELECT * FROM table WHERE fieldx = old-value;
UPDATE table SET fieldx = new-value
WHERE fieldx = old-value;
by the way, “old-value” in
WHERE fieldx = old-value;
is not case sensitive.
change only a sub-string, a part of the content in a field:
SELECT * FROM table
WHERE fieldx LIKE '%what you want to replace%' ;
UPDATE table
SET fieldx = REPLACE(fieldx,
'what you want to replace', 'something new')
WHERE id=101
(101 = some one record # you can confirm or fix if you wrecked it.)
then, clean up all of them:
UPDATE table
SET fieldx = REPLACE(fieldx,
'what you want to replace', 'something new')
WHERE fieldx LIKE '%what you want to replace%' ;
to see the other records: NOT LIKE
SELECT * FROM table
WHERE fieldx NOT LIKE '%what you want to replace%'
change [fielda] to 4 where it is not empty or “NULL”:
or length(fielda) > 0
SELECT * FROM [table] WHERE [fielda] <> ''
UPDATE [table] SET [fielda] = 4 WHERE [fielda] <> ''
( != will often work for <> but is not an ISO standard )
Deleting records, “records older than …”, in the UNIX time format,
(ex: timecreated = 1234567890)
SELECT id, userid, event, timecreated, FROM_UNIXTIME(timecreated)
FROM log_table
WHERE timecreated < UNIX_TIMESTAMP(CURDATE() - INTERVAL 90 DAY ) ;
DELETE FROM log_table
WHERE timecreated < UNIX_TIMESTAMP(CURDATE() - INTERVAL 90 DAY ) ;
To find and delete lesson-page records and then the lesson records where
the course-class has already been deleted.
SELECT l.course, SUBSTRING(l.name, 1, 40) AS LessonName, lp.title AS Page_Title,
SUBSTRING(lp.contents, 1, 30) AS pageContents, LENGTH(lp.contents) AS pageLength
FROM mdl_lesson_pages AS lp
JOIN mdl_lesson AS l
ON lp.lessonid = l.id
LEFT JOIN mdl_course AS c
ON l.course = c.id
WHERE c.id IS NULL
ORDER BY l.course, l.id, lp.id ;
DELETE mdl_lesson_pages
FROM mdl_lesson_pages
JOIN mdl_lesson AS l
ON mdl_lesson_pages.lessonid = mdl_lesson.id
LEFT JOIN mdl_course AS c
ON l.course = c.id
WHERE c.id IS NULL
note: you cannot use an "AS" alias-abbreviation for a table from which you are
deleting records. Tables joined to it, you can.
SELECT l.course, SUBSTRING(l.name, 1, 40) AS LessonName,
FROM mdl_lesson AS l
LEFT JOIN mdl_course AS c
ON l.course = c.id
WHERE c.id IS NULL
ORDER BY l.course, l.id ;
DELETE mdl_lesson
FROM mdl_lesson
LEFT JOIN mdl_course AS c
ON mdl_lesson.course = c.id
WHERE c.id IS NULL
INSERT INTO
The general syntax is:
INSERT INTO table-name (column-names)
VALUES (values)
example:
$namef = addslashes(trim($_POST['namef'])) ;
$namel = addslashes(trim($_POST['namel'])) ;
# and validate the rest also etc. before inserting #
$query = 'INSERT INTO database.user
( namef, namel, address, city, state )
VALUES ( "'.$namef.'", "'.$namel.'", "'.$address.'", "'.$city.'", "'.$state.'" ) ';
addslashes() is important not only to stop hackers but also to avoid crashing on a person or town named O'Dell or an address like "123 d'Angelo Street" or a person who gives you [ Robert "Bob" ] for their first name.
Two ways to insert new records where one doesn't already exist:
Note: either way, without the "not exists" or the "is null",
where duplicate records are not allowed, the script will terminate
on the first record it encounters.
Meaning, you cannot leave it off and expect the script to continue after failing
to write a record.
INSERT INTO #table1 (Id, guidd, TimeAdded, ExtraData)
SELECT Id, guidd, TimeAdded, ExtraData
FROM #table2
WHERE NOT EXISTS (Select Id, guidd From #table1 WHERE #table1.id = #table2.id)
INSERT INTO table1 (field1, field2, field3, field4)
SELECT t2.field1, t2.field2, t2.field3, t2.field4
FROM table2 AS t2
LEFT JOIN table1 AS t1
ON t1.id = t2.id
WHERE t1.id IS NULL
INSERT INTO table1 (field1, field2, field3, field4)
SELECT t2.field1, t2.field2, t3.field3, t3.field4
FROM table2 AS t2
JOIN table3 AS t3
ON t2.userid = t3.userid
LEFT
JOIN table1 AS t1
ON t1.id = t2.id
WHERE t1.id is null
A person can use all but the first [INSERT INTO ... ] line for testing.
update columns across databases
update columns from 2 databases
UPDATE database2.table AS db2_tbl
JOIN database1.table AS db1_tbl
ON db2_tbl.id = db1_tbl.id
SET db2_tbl.column = db1_tbl.column
WHERE db2_tbl.column = 'content you wish to replace'
a wordpress example:
shorten full-path hard-coded internal-external links to internal relative links
SELECT * FROM pgm_posts WHERE guid LIKE 'http://www.website.com/blog/%'
UPDATE pgm_posts
SET guid = REPLACE(guid, 'http://www.website.com/blog/', '')
WHERE guid LIKE 'http://www.website.com/blog/%'
SELECT ID, post_author, post_date, post_title, post_name,
post_modified, post_parent, guid, post_type
FROM pgm_posts
WHERE post_author = 3
a range:
SELECT * FROM user_answers
WHERE userkey > 1
AND userkey < 22
DELETE FROM user_answers
WHERE userkey > 1
AND userkey < 22
WHERE userkey > 1
AND userkey < 22
is the same as
WHERE userkey BETWEEN 2 AND 21
you want to replace Joe with Harry in [field] in rec.29 :
SELECT * FROM [table] WHERE [field] LIKE '%Joe%'
UPDATE [table]
SET [field] = REPLACE([field], 'Joe', 'Harry')
WHERE key_id = 29
RLIKE: supports regular expressions
find and display duplicate records, if there are any,
so that you can delete the duplicates. example:
SELECT DISTINCT h1.phone
FROM table_one as h1
JOIN table_one as h2
WHERE h1.phone = h2.phone
ORDER BY h1.phone
# another, more verbose, way to
# display duplicate records, if there are any (only works in mysql 5.x) :
SELECT h1.orderid
FROM oltohead as h1
HAVING orderid IN
(SELECT h2.orderid
FROM oltohead as h2
GROUP BY h2.orderid
HAVING count(*) > 1 )
ORDER BY h1.orderid
SELECT lp.lessonid, lp.prevpageid, lp.id AS pageid, lp.nextpageid,
lp.qtype, lp.title
FROM mdl_lesson_pages AS lp
WHERE lp.lessonid = 1388
ORDER BY lp.id
SELECT lp.lessonid, lp.prevpageid, lp.id AS pageid, lp.nextpageid,
lp.qtype, lp.title,
la.id, la.response
FROM mdl_lesson_pages AS lp
LEFT OUTER JOIN mdl_lesson_answers AS la
ON lp.lessonid = la.lessonid
AND lp.id = la.pageid
WHERE lp.lessonid = 1388
GROUP BY lp.id
update data in one table with data from another table:
UPDATE table_u2 AS u2
JOIN table_u1 AS u1
ON u2.userkey = u1.userkey
SET u2.field_x = u1.field_x,
u2.field_1 = u1.field_1,
u2.field_2 = u1.field_2,
u2.field_3 = u1.field_3,
u2.field_4 = u1.field_4,
u2.field_5 = u1.field_5,
u2.field_6 = u1.field_6
WHERE u2.userkey < 200
UPDATE table
SET points = IF(type = 'points', points + 1, points),
bonus = IF(type <> 'points', bonus + 1, bonus)
WHERE id > 170
UPDATE `table` SET `uid` =
CASE
WHEN id = 1 THEN 2952
WHEN id = 2 THEN 4925
WHEN id = 3 THEN 1592
ELSE `uid`
END
update a field, column, depending on another table's data
some sample complex updates. Do a select first, to be safe.
UPDATE mdl_lesson_pages AS p
SET qtype = 20
WHERE p.id NOT IN
(SELECT pageid FROM mdl_lesson_answers)
UPDATE TABLE_1
LEFT OUTER JOIN TABLE_2
ON TABLE_1.COLUMN_1= TABLE_2.COLUMN_2
SET TABLE_1.COLUMN = EXPR
WHERE TABLE_2.COLUMN2 IS NULL
join 2 tables: table1 and table2
UPDATE table1
JOIN table2
ON table1.id=table2.id
SET table1.price=table2.price
WHERE table1.price<>table2.price
update a field, column, from another database (a backup copy?)
to compare, select columns, fields, across 2 databases
select database.table.field = select database.table.column
SELECT db1_tbl.column AS column1, db2_tbl.column AS column2
FROM database2.table AS db2_tbl
JOIN database1.table AS db1_tbl
ON db2_tbl.id = db1_tbl.id
WHERE db2_tbl.column = 'content you wish to replace'