Last active
May 31, 2019 09:48
-
-
Save sidor1989/e834b1034f245a4ee68f62d5aae24720 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#удаляем дубли записей | |
ALTER IGNORE TABLE modx_paysee_resource_user_list ADD UNIQUE KEY(user_id) ORDER BY stopdate DESC; | |
#меняем в базе значения с регуляркой | |
UPDATE wp_site_content AS c | |
SET c.pagetitle = REPLACE( c.pagetitle, 'x',' H ') | |
WHERE c.pagetitle REGEXP 'Имплантат AnyRidge Ø [0-9.]{2,5}x[0-9.]{2,5} мм' | |
#вставка из одной таблицы в другую без дублей | |
INSERT INTO `modx_paysee_resource_user_list_my2`(`resource_id`, `user_id`, `active`, `startdate`, `stopdate`, `properties`) SELECT resource_id, user_id, active, MIN(startdate), MAX(stopdate), properties FROM modx_paysee_resource_user_list GROUP BY user_id | |
#удаляем дубли записей и оставляем с наибольшим stopdate | |
DELETE u FROM modx_paysee_resource_user_list u LEFT JOIN modx_paysee_resource_user_list u2 ON u.user_id = u2.user_id where u2.stopdate > u.stopdate; | |
#перенос из одной таблицы в другую | |
REPLACE INTO modx_payandsee_subscriptions (client,status,startdate,stopdate) | |
SELECT user_id,active,startdate,stopdate FROM modx_paysee_resource_user_list | |
#меняем контент | |
UPDATE `modx_payandsee_subscriptions` SET `content`=1 | |
#меняем статусы в таблице клиентов | |
UPDATE modx_payandsee_clients AS u SET | |
u.status=2 | |
WHERE u.status=1; | |
#меняем статусы в таблице подписки | |
UPDATE modx_payandsee_subscriptions AS u SET | |
u.status=3 | |
WHERE u.status=0; | |
UPDATE modx_payandsee_subscriptions AS u SET | |
u.status=2 | |
WHERE u.status=1; | |
============================================================================== | |
============================================================================== | |
DELETE bitrixsendex_subscribers FROM bitrixsendex_subscribers | |
JOIN bitrixusers | |
ON bitrixsendex_subscribers.user_id = bitrixusers.id | |
WHERE bitrixusers.active = 0 | |
#записи с хекслета начало | |
#PostgreSQL | |
CREATE TABLE users ( | |
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, | |
username text UNIQUE NOT NULL, | |
email text NOT NULL, | |
created_at date NOT NULL | |
); | |
CREATE TABLE topics ( | |
id integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY, | |
users_id integer REFERENCES users (id), | |
body text NOT NULL, | |
created_at date NOT NULL | |
); | |
#записи с хекслета конец |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment