Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAE1g-0006Tr-72 for pgsql-sql@arkaria.postgresql.org; Wed, 19 Jan 2022 16:41:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nAE1e-0005x9-DH for pgsql-sql@arkaria.postgresql.org; Wed, 19 Jan 2022 16:41:34 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nAE1e-0005x0-28 for pgsql-sql@lists.postgresql.org; Wed, 19 Jan 2022 16:41:34 +0000 Received: from mail-pf1-x42a.google.com ([2607:f8b0:4864:20::42a]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nAE1b-0006jn-QQ for pgsql-sql@lists.postgresql.org; Wed, 19 Jan 2022 16:41:33 +0000 Received: by mail-pf1-x42a.google.com with SMTP id r5so2808778pfl.2 for ; Wed, 19 Jan 2022 08:41:31 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to:content-transfer-encoding; bh=VdVdu1gsTo1F56HK3ibd2mz9U/54SMUOrpeNnCHjwtw=; b=IZtQoEjHaQzAw22IKibbtQXN4BmYc1QSbQwPQ1gqFObIKM250BXrz4+NsE6pk+WviQ c3SZN0mb/N+hJPwS0diajpQXxpcxGy1wu+U4/AK9fjU2E7QGcD++TgSuJJqaO5a9UxRp +m9xAdgKdocRCai5Xlutgk+fOih4GPl7l5/CDKPfjOR5I00ojWVgf4XNGLYS0tN/eTRs wBaY5x+ppGMjUmDiUyx/pQ6ugmtJwOhiWhEZe4jepS6buY/SYbG8y8MfCs76Gzv4ub+M Ofb9pWOmUHGiKvrRKdZPsfZ8Qu3bV7xyFoWdBoQ2V2I2ezrp4nFWgwrxgYYU+NzIXjCa SfKQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to :content-transfer-encoding; bh=VdVdu1gsTo1F56HK3ibd2mz9U/54SMUOrpeNnCHjwtw=; b=wQ1wKFNMQUzqizJ5AcASe32ffKvLxmSTZFv6OZDEDHcIbH/Afcmg1eIq9Pzi/WMY1N u1YGN2k1Vjd7xbca/7f4t7tv/pOXwPQSlDhNiFkNmDxigjkrC3Yajnywm42fIvBIvYuV HCUGCk4z5xkMIlGYVp7Ek2P1KeLHXAdefMItv6UYqcGSYXQo41sI30ZCFPPQJrTWtE5u gVDU4Gy1kVSYFcKCE9Qjj8AqCqejTRxpFe5b8aV1+IdTOuq3tImjpK9SmYupgF7VuV3P Loyl4OFR0/8lhX4CQPIh+h78GapS/CrRIl9t3Dl3gttMWJ7AYEEHLTE2HSR0VgXAnRco k0vg== X-Gm-Message-State: AOAM530y13vGwQOpOtp3XncfBtOkf+4XiSFvrpYkL402BAoqRaicyz25 B0+URrEYaEMK2i6ENNnClZD2pEK5bs4= X-Google-Smtp-Source: ABdhPJyZvfH5wbX7veceDvGU7FdGYcl2vwhRNEitPM3VutO9AKwX6nMljqzTmUxKzw8fwCbEgQU4sQ== X-Received: by 2002:a62:cd02:0:b0:4bd:8f59:dc4e with SMTP id o2-20020a62cd02000000b004bd8f59dc4emr31680136pfg.64.1642610490521; Wed, 19 Jan 2022 08:41:30 -0800 (PST) Received: from [10.0.0.94] (c-73-63-59-118.hsd1.ut.comcast.net. [73.63.59.118]) by smtp.gmail.com with ESMTPSA id e14sm144877pfv.177.2022.01.19.08.41.30 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 19 Jan 2022 08:41:30 -0800 (PST) Message-ID: <6295d658-71b9-79b9-17cc-36c5525673cc@gmail.com> Date: Wed, 19 Jan 2022 09:41:29 -0700 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.5.0 Subject: Re: Clean up shop database Content-Language: en-CA To: pgsql-sql@lists.postgresql.org References: <20220119143718285650.48772b27@klingler.net> <43C34BCB-1690-40DE-852F-72D289096CB2@gmail.com> <20220119145738389655.606222b1@klingler.net> From: Rob Sargent In-Reply-To: <20220119145738389655.606222b1@klingler.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > Hmm..almost forgot..... > > Now how would I turn this into an update statement based on the > previous query? > update what?  A CTE comes to mind but maybe update product p set some=thing where p.productid = m.productid from (select p.productid,max(o.orderdate) from product p join orderitem t on p.productid = t.orderitems2productid join order o on t.orderitems2orderid = o.orderid group by p.productid having max(o.orderdate < 'January 1 2021') as m