Skip to content

Instantly share code, notes, and snippets.

@n8finch
Created May 10, 2017 06:02
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save n8finch/35d3525b0e6530fd1c9fbbfff9f28ad5 to your computer and use it in GitHub Desktop.
Save n8finch/35d3525b0e6530fd1c9fbbfff9f28ad5 to your computer and use it in GitHub Desktop.
the following SQL would duplicate the redirects that end with '/', making a copy without a slash.
INSERT INTO wp_redirection_items(
`url`,
`regex`,
`position`,
`last_count`,
`last_access`,
`group_id`,
`status`,
`action_type`,
`action_code`,
`action_data`,
`match_type`,
`title`
)
SELECT
SUBSTRING(items.url, 1, LENGTH(items.url)-1) AS url,
items.regex,
@pos := @pos + 1 AS postion,
0 AS last_count,
'0000-00-00 00:00:00' AS last_access,
items.group_id,
items.status,
items.action_type,
items.action_code,
items.action_data,
items.match_type,
items.title
FROM wp_redirection_items AS items
JOIN (SELECT @pos := MAX(sub.position) FROM wp_redirection_items AS sub) r
LEFT JOIN wp_redirection_items AS matched ON
matched.url = SUBSTRING(items.url, 1, LENGTH(items.url)-1)
AND matched.regex = items.regex
AND matched.group_id = items.group_id
AND matched.status = items.status
AND matched.action_type = items.action_type
AND matched.action_code = items.action_code
AND matched.match_type = items.match_type
WHERE
items.group_id = 1
AND items.url LIKE '/%-%/'
AND items.regex = 0
AND items.status = 'enabled'
AND items.action_code = 301
AND items.match_type = 'url'
AND matched.id IS NULL
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment