CODEWITHSUNDEEP
sqloracle-database
Monty Swanson
Monty Swanson

Reputation: 795

Updating data to a table using select statement

I have 4 tables:

**appdetail** 
id, alocation

**alocation**
id, appdetail

**place**
id, name

**location_place**
alocation, place

location_place.alocation points to alocation.id location_place.place points to place.id

I want to copy the values of location_place.alocation to appdetail.alocation. I tried the solution from https://stackoverflow.com/a/8963158/815859. However, I am getting "SQL command not properly ended" error. My query is 

update appdetail set 
alocation =i.alocation
from 
(select lp.alocation
from location_place lp,
alocation apl,
appdetail ad
where
lp.alocation = apl.id
and 
apl.id in (547,294)
and
ad.id = apl.appdetail
)i
where appdetail.alocation = i.alocation

The DBFiddle is at https://www.db-fiddle.com/f/8SB3tHxWVY7NbCyzpKLhRG/4

Upvotes: 0

Views: 49

Answers (2)

Popeye
Popeye

Reputation: 35900

You can use MERGE INTO for it:

MERGE INTO appdetail AD
USING (
SELECT ID, appdetail
  FROM LOCATION L
  JOIN location_place LP
  ON (LP.LOCATION = L.ID)
-- WHERE L.id in (547,294) 
) I ON (AD.ID = I.ID)
WHEN MATCHED THEN 
UPDATE SET AD.LOCATION = I.ID;

db<>fiddle demo

Cheers!!

-- UPDATE --

as per requirement changed and communicated as in comment, You can use the following query:

MERGE INTO appdetail AD
USING (
SELECT ID, appdetail, LP.PLACE
  FROM LOCATION L
  JOIN location_place LP
  ON (LP.LOCATION = L.ID) 
) I ON (AD.ID = I.ID)
WHEN MATCHED THEN 
UPDATE SET AD.LOCATION = I.PLACE;

db<>fiddle demo updated

Cheers!!

Upvotes: 1

APC
APC

Reputation: 146209

FROM is not part of the Oracle UPDATE syntax.

What I think you intended is this: 

 update appdetail ad
 set ad.alocation = (select lp.alocation
                     from   location_place lp,
                            alocation apl
                      where lp.alocation  = apl.id
                      and   apl.id       in (547,294)
                      and   ad.id         = apl.appdetail )

Upvotes: 0

Related Questions