Subselect in Update Statment with where

Question

How can I make this PHP script in a one Single SQL statement?

$sql = 'SELECT oxtprice from oxarticles where oxparentid = ? and nrseriesarticle = 1';
        $price = DatabaseProvider::getDb()->getOne($sql, [$id]);

        if ($price) {
            $updateSql = "Update oxarticles SET nrseriestprice = ? WHERE oxid = ? and oxparentid = ''";
            DatabaseProvider::getDb()->execute($updateSql, [$price, $id]);

I want something like this but it didn't work

UPDATE oxarticles SET 
nrseriesprice = (SELECT oxprice from oxarticles where oxparentid = ? and nrseriesarticle = 1) 
WHERE oxid = ?

Answer 1

You only need one query like below

CREATE TABLE parent (oxid int ,nrseriesprice DECIMAL (10,2))

INSERT INTO parent VALUES (1,NULL)

CREATE TABLE oxarticles (oxprice DECIMAL(19,2), oxparentid int,nrseriesarticle int)

INSERT INTO oxarticles VALUES (19.2,1,1)

UPDATE parent p SET 
nrseriesprice = (SELECT oxprice from oxarticles where oxparentid = p.oxid and nrseriesarticle = 1) 
WHERE p.oxid = 1

SELECT * FROM parent

oxid | nrseriesprice
---: | ------------:
   1 |         19.20

db<>fiddle here