SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE SHOPS ( SHOP INT PRIMARY KEY, DOC_NUMBER VARCHAR(20), DAY DATE, SHIPMENT VARCHAR(10) ); CREATE TABLE WAREHOUSE ( WAREHOUSE VARCHAR(20), ID VARCHAR(20) PRIMARY KEY, DOCUMENT VARCHAR(20), MONTH INT ); 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'); 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)) FROMSHOPS 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;