SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE SHOPS ( SHOP INT, DOC_NUMBER VARCHAR(50), DAY DATE, SHIPMENT VARCHAR(50) ); INSERT INTO SHOPS (SHOP, DOC_NUMBER, DAY, SHIPMENT) VALUES (1220, '1123-4553-08', '2021-12-27', 'COU'), (1221, '0811-4553-08', '2021-11-24', 'PNT'), (1222, '0043-4553-14', '2021-12-25', 'LCTN'), (1320, '2333-4553-16', '2021-10-20', 'PNT'), (1330, '1000-4553-10', '2021-12-27', 'COU'), (1450, '0999-4553-14', '2021-09-26', 'PNT'), (1520, '1499-4553-08', '2021-12-22', 'COU'); CREATE TABLE WAREHOUSE ( WAREHOUSE VARCHAR(15), ID INT, DOCUMENT VARCHAR(15), MONTH INT ); INSERT INTO WAREHOUSE (WAREHOUSE, ID, DOCUMENT, MONTH) VALUES ('',11231521,'', 0), ('',08111221,'', 0), ('',00431022,'', 0), ('',23331320,'', 0), ('',10001330,'', 0), ('',09991450,'', 0), ('',14991520,'', 0); WITH T1 AS (SELECT SHOPS.SHOP FROM SHOPS JOIN WAREHOUSE ON SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP) UPDATE WAREHOUSE SET WAREHOUSE = CASE WHEN WAREHOUSE.WAREHOUSE = '' AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1) THEN (SELECT SUBSTRING(SHOPS.DOC_NUMBER, 11, 2) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP) END, DOCUMENT = CASE WHEN WAREHOUSE.DOCUMENT = '' AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1) THEN (SELECT (SUBSTRING(SHOPS.DOC_NUMBER, 6, 7) + '-' + SUBSTRING(CAST(SHOPS.DAY AS CHAR), 6, 5)) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4) = SHOPS.SHOP) END, MONTH = CASE WHEN WAREHOUSE.MONTH = 0 AND SUBSTRING(WAREHOUSE.ID, 5, 4) IN (SELECT SHOP FROM T1) THEN (SELECT (SUBSTRING(CAST(SHOPS.DAY AS CHAR), 6, 2)) FROM SHOPS WHERE SUBSTRING(WAREHOUSE.ID, 5, 4)= SHOPS.SHOP) END; SELECT COUNT(WAREHOUSE) FROM WAREHOUSE WHERE WAREHOUSE IS NOT NULL AND SUBSTRING(WAREHOUSE.DOCUMENT, 12, 2) >= 24;