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 = ?

I think you really need to explain what the columns in this `oxarticles` represent. I can only guess what the relation is between a row with a certain value in `oxid` and another row with the same value in `oxparentid`. It is also well known that just saying that something "didn't work" is not very helpful. Didn't work how? — KIKO Software, Feb 11 '22 at 11:51
see https://stackoverflow.com/questions/2114534/mysql-syntax-for-join-update — Salman A, Feb 11 '22 at 11:55

score 1 · Answer 1 · answered Feb 11 '22 at 12:05

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

Subselect in Update Statment with where

1 Answers1