-- updating a table during runtime of the script with a join ALTER TABLE shops_per_city ADD merchant_type DOUBLE; /*ALTER TABLE shops_per_city ADD city_latitude DOUBLE*/ UPDATE shops_per_city AS shop INNER JOIN city_list AS city ON shop.city_id = city.city_id SET shop.city_latitude = city.city_latitude; -- selecting the list for a where statement with a subquery SELECT * FROM shopstation WHERE Dealer_ID_Long IN (select Dealer_ID_Long FROM shop_list); -- also possible to join on a subquery (notice that there is no ; in the subquery) SELECT *, -- this select needs to have all columns of the result table FROM shopcounter_category_daily_v2 AS shop INNER JOIN (SELECT * FROM weather_owm_daily_2014 WHERE cityId = 2761369) AS weather ON shop.RECORD_DATE = weather.DATE; -- update a column using CASE UPDATE creditcard_merchtype SET online = (CASE WHEN Merchantname LIKE "%.at%" THEN 1 WHEN Merchantname LIKE "%.com%" THEN 1 ELSE 0 END); -- alter column name ALTER TABLE creditcard_at_v1 CHANGE cityId city_id INT; -- creating temporary table for later use: CREATE TEMPORARY TABLE id_list AS SELECT COUNT(*) AS days, dealer_name, DEALER_ID, Dealer_ID_Long FROM shopcounter WHERE YEAR(RECORD_DATE) > 2017 GROUP BY dealer_name, DEALER_ID, Dealer_ID_Long HAVING days > 752; CREATE TABLE shopcounter_stable AS SELECT * FROM shopcounter WHERE Dealer_ID_Long IN (SELECT Dealer_ID_Long FROM id_list) && YEAR(RECORD_DATE) > 2017;
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter