Updating data to a table using select statement

Question

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

Question is confusing. Can you please share sample data for each table and expected output? — Popeye, Aug 22 '19 at 12:33
I have updated the question with a DBFiddle link. Please check. — Monty Swanson, Aug 22 '19 at 13:02

Popeye · Accepted Answer · 2019-08-23T06:34:50.337

1

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!!

edited Aug 23 '19 at 06:34

answered Aug 22 '19 at 13:16

Popeye

35,427
4
10
31

I stated the requirement incorrectly. What i am looking for is inserting the value of LOCATION_TABLE.PLACE (which is linked to place.id) to APPDETAIL.LOCATION. – Monty Swanson Aug 23 '19 at 06:22

score 0 · Answer 2 · answered Aug 22 '19 at 12:40

0

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 )

answered Aug 22 '19 at 12:40

APC

144,005
19
170
281

Note: this is just a guess. If it doesn't work for you please **edit your question** to include some sample data and desired output derived from that sample. – APC Aug 22 '19 at 12:42
I have updated the question with a DBFiddle link. Please check. – Monty Swanson Aug 22 '19 at 13:02

Updating data to a table using select statement

2 Answers2