CODEWITHSUNDEEP
sqloracle-database
user1902849
user1902849

Reputation: 1141

Update column values by other column values

How to write a query which will update column A on table A with the data from table B ? Table A and table B are linked by ID. ID is a primary key of table A and foreign key of table B. I need to summarize the amount column in table b by id, and then update the sum column in table A by ID.

QUERY

create table A
(Id number,
SumAmount number
);

insert into A 
SELECT 1, NULL FROM DUAL
UNION All
SELECT 2, NULL FROM DUAL
UNION ALL
SELECT 3, NULL FROM DUAL
UNION All
SELECT 4, NULL FROM DUAL
UNION ALL
SELECT 5, NULL FROM DUAL
UNION All
SELECT 6, NULL FROM DUAL


create table B
(Id number,
Amount number
);


insert into B
SELECT 1, 100 FROM DUAL
UNION All
SELECT 1, 200 FROM DUAL
UNION ALL
SELECT 1, 320 FROM DUAL
UNION All
SELECT 2, 230 FROM DUAL
UNION ALL
SELECT 4, 246 FROM DUAL
UNION All
SELECT 4, 212 FROM DUAL

I've wrote a query for this but it doesn't work. Any helps are appreciated.

update a
set a.sumamount = (
select sum(b.amount)
from b group by b.id
where a.id = b.id
)

Upvotes: 0

Views: 83

Answers (2)

Gauraw Tiwari
Gauraw Tiwari

Reputation: 11

update A set 
SumAmount = (
select sum(B.Amount)
from B group by B.Id
HAVING (A.Id=B.Id) )

Upvotes: 1

Andomar
Andomar

Reputation: 238126

Your query is alright, if you swap group by and where:

update a
set a.sumamount = (
select sum(b.amount)
from b
where a.id = b.id
group by b.id
);

Example at SQL Fiddle.

Upvotes: 1

Related Questions