Noel ST Noel ST - 2 months ago 5
SQL Question

SQL Updating a particular rows based on the ID in another table

I have two tables

Price
RET_ID DBS_ID RRP

Database
DBS_ID PRO_ID


I would like to use UPDATE such that I can increase RRP in the Price table by 20% if the PRO_ID in the Database table is = 1

UPDATE
( SELECT RRP
FROM PRICE
JOIN database
ON database.dbs_id = price.dbs_id
)
SET rrp = 100
WHERE (database.pro_ID = 1);


I've been trying all manner of INNER JOIN ... ON to no avail.
Thanks.

Answer

You can put the filtering condition in the where clause. Here is a method using exists:

update price p
    set rpr = rpr * 1.2
    where exists (select 1
                  from database d
                  where d.dbs_id = p.dbs_id and d.pro_id = 1
                 );

Here is another way using in:

update price p
    set rpr = rpr * 1.2
    where p.dbs_id in (select d.dbs_id
                       from database d
                       where d.pro_id = 1
                      );