Update Multiple records in a table
I have two tables. Person and Person_Date
SQL> desc person;
Name
-------------------
ID
DOB_DAY
DOB_MONTH
DOB_YEAR
SQL> desc person_Date;
Name
----------------
ID
DATE_TYPE_ID
DAY
MONTH
YEAR
PERSON_ID
There is a one to one match as Person.ID = Person_Date.Person_ID
Looking for SQL to UPDATE Table PERSON, columns DOB_DAY, DOB_MONTH, DOB_YEAR with Values from Table Person_Date, columns Day, Month, Year. There are over 50, 000 records.
SQL> desc person;
Name
-------------------
ID
DOB_DAY
DOB_MONTH
DOB_YEAR
SQL> desc person_Date;
Name
----------------
ID
DATE_TYPE_ID
DAY
MONTH
YEAR
PERSON_ID
There is a one to one match as Person.ID = Person_Date.Person_ID
Looking for SQL to UPDATE Table PERSON, columns DOB_DAY, DOB_MONTH, DOB_YEAR with Values from Table Person_Date, columns Day, Month, Year. There are over 50, 000 records.
You could also use merge -
merge into person p
using person_date d
on (p.id = d.person_id)
when matched then
update set
p.dob_day=d.day,
p.dob_month=d.month,
p.dob_year=d.year;
merge into person p
using person_date d
on (p.id = d.person_id)
when matched then
update set
p.dob_day=d.day,
p.dob_month=d.month,
p.dob_year=d.year;
ASKER
I tried this, with slight modification but got error.
SQL> Update Person
2 set (dob_day, dob_month, dob_year) = (Select day, month, year
From person_date
where Person_Date.Person_ID = Person.ID
And Person_Date.DATE_TYPE_ID = 1000);
3 4 5 set (dob_day, dob_month, dob_year) = (Select day, month, year
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
SQL> Update Person
2 set (dob_day, dob_month, dob_year) = (Select day, month, year
From person_date
where Person_Date.Person_ID = Person.ID
And Person_Date.DATE_TYPE_ID = 1000);
3 4 5 set (dob_day, dob_month, dob_year) = (Select day, month, year
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
Then there is not a one to one relationship between PERSON_DATE.PERSON_ID and PERSON.ID.
This query will return results:
select person_id
from person_date
where date_type_id = 1000
group by person_id
having count(1) > 1;
This query will return results:
select person_id
from person_date
where date_type_id = 1000
group by person_id
having count(1) > 1;
If you want to try the merge method, it needs to be modified slilghtly since you've added a condition.
merge into person p
using
(select person_id, day, month, year
from person_date
where date_type_id = 1000) d
on (p.id = d.person_id)
when matched then
update set
p.dob_day=d.day,
p.dob_month=d.month,
p.dob_year=d.year;
merge into person p
using
(select person_id, day, month, year
from person_date
where date_type_id = 1000) d
on (p.id = d.person_id)
when matched then
update set
p.dob_day=d.day,
p.dob_month=d.month,
p.dob_year=d.year;
>>There is a one to one match as Person.ID = Person_Date.Person_ID<<
Apparently not. You might be able to limit that using distinct, but that also wouldn't work if there are different day, month, and year values for the same id, in which case you may want to decide what values are desired based on some criteria.
Apparently not. You might be able to limit that using distinct, but that also wouldn't work if there are different day, month, and year values for the same id, in which case you may want to decide what values are desired based on some criteria.
ASKER
I tried suggestion by awking00. I get error below. I think we are pretty close.
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
--########################
Running Jonson's select person_id
from person_date
where date_type_id = 1000
group by person_id
having count(1) > 1;
-- I get 118 records. which are duplicated. Hence update the duplicates as well.
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
--########################
Running Jonson's select person_id
from person_date
where date_type_id = 1000
group by person_id
having count(1) > 1;
-- I get 118 records. which are duplicated. Hence update the duplicates as well.
How do you decide which duplicate to use as the source of the update?
If I want to update person id 1 in the PERSON table and there are 2 records in the PERSON_DATE table for PERSON_ID 1, which record do you use? You cannot use them both.
If I want to update person id 1 in the PERSON table and there are 2 records in the PERSON_DATE table for PERSON_ID 1, which record do you use? You cannot use them both.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
This query returns 118 records.
select person_id
from person_date
where date_type_id = 1000
group by person_id
having count (1)> 1;
I want to update person_date table so that the date_type id in above query is set to 1001.
Only 118 records should be updated.
select person_id
from person_date
where date_type_id = 1000
group by person_id
having count (1)> 1;
I want to update person_date table so that the date_type id in above query is set to 1001.
Only 118 records should be updated.
I'm a little confused. Your original post said you wanted to update PERSON. Now you want to update PERSON_DATE. Which is it?
Also, your original post says 50,000 rows will be update, now you are saying only 118.
Can you clearly state the requirements here?
Also, your original post says 50,000 rows will be update, now you are saying only 118.
Can you clearly state the requirements here?
>>This query returns 118 records. <<
We know. You already stated that. I wanted to see the results of the query having count(*) > 1 when the day, month, and year are included in the group by. If it returns no rows then that tells you every combination of the day, month, and year are unique and you need to determine which of those values should be used for the update.If it returns fewer than 118 rows then that tells you some combinations of the day, month, and year are unique and you still need to determine which of those values should be used for the update. If it returns 118 rows, then those values are the same for each of the 118 ids and a simple distinct keyword in the using statement of the merge will be adequate.
We know. You already stated that. I wanted to see the results of the query having count(*) > 1 when the day, month, and year are included in the group by. If it returns no rows then that tells you every combination of the day, month, and year are unique and you need to determine which of those values should be used for the update.If it returns fewer than 118 rows then that tells you some combinations of the day, month, and year are unique and you still need to determine which of those values should be used for the update. If it returns 118 rows, then those values are the same for each of the 118 ids and a simple distinct keyword in the using statement of the merge will be adequate.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
set (dob_day, dob_month, dob_year) = (select day, month, year from person_date where person.id = person_date.person_id);