CODEWITHSUNDEEP
sqloracle-database
user17237422
user17237422

Reputation:

How do I UPDATE with INNER JOIN

I want to SET the price of SOFTWARE to some other value depending on two conditions:

  1. SOFTWARE that have a name that starts with M.

  2. SOFTWARE that are installed in the INSTALLER table

Code:

SELECT N_SOF,NAMESOF FROM SOFTWARE;

      N_SOF NAMESOF
---------- -------------------------
        64 WINDOWS
        70 WINDOWS
        71 MAC
        72 MAC
        73 MAC

SELECT N_INS,N_SOF FROM INSTALLER;

    N_INS       N_SOF
---------- ----------
         3         71
         3         72
        12         73

I tried this, in SQL*PLUS (ORACLE), but it didn't work.

SQL> UPDATE SOFTWARE
  2  SET PRICE= PRICE+100
  3  INNER JOIN INSTALLER I ON I.NLOG=SOFTWARE.N_SOF
  4  WHERE SOFTWARE.NAMESOF='M%' AND I.N_INS=3;

I got this error message

INNER JOIN INSTALLER I ON I.NLOG=LOGICIEL.NLOG
*
ERROR at line 3:
ORA-00933: SQL command not properly ended

Upvotes: 1

Views: 68

Answers (2)

MT0
MT0

Reputation: 167822

Use UPDATE with an EXISTS clause:

UPDATE SOFTWARE s
  SET PRICE= PRICE+100
WHERE NAMESOF LIKE 'M%'
AND   EXISTS(SELECT 1
             FROM   INSTALLER i
             WHERE  i.N_SOF=s.N_SOF
             AND    i.N_INS=3);

Which, for the sample data:

CREATE TABLE software (N_SOF, NAMESOF, price) AS
SELECT 64, 'WINDOWS', 0 FROM DUAL UNION ALL
SELECT 70, 'WINDOWS', 0 FROM DUAL UNION ALL
SELECT 71, 'MAC',     0 FROM DUAL UNION ALL
SELECT 72, 'MAC',     0 FROM DUAL UNION ALL
SELECT 73, 'MAC',     0 FROM DUAL;

CREATE TABLE INSTALLER (N_INS, N_SOF) AS
SELECT  3, 71 FROM DUAL UNION ALL
SELECT  3, 72 FROM DUAL UNION ALL
SELECT 12, 73 FROM DUAL;

After the update then:

SELECT * FROM software

Outputs:

N_SOF NAMESOF PRICE
64 WINDOWS 0
70 WINDOWS 0
71 MAC 100
72 MAC 100
73 MAC 0

db<>fiddle here

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

UPDATE SOFTWARE
SET PRICE= PRICE+100
WHERE
  SOFTWARE.NAMESOF LIKE 'M%' 
  AND EXISTS (
    SELECT *
      FROM INSTALLER I
     WHERE I.NLOG=SOFTWARE.N_SOF
       AND I.N_INS=3
  )

Upvotes: 2

Related Questions