occal [Problem Resolution]ora-01427: Single-line subquery returns multiple rows

Source: Internet
Author: User
Tags sql error

I am asked a question, the situation is as follows:
He is going to update Divide_stat's New_amount field with the New_amount field based on Divide_act_channel_day.
Conditions associated with two tables: Day=log_time,channel=channel

--sql as follows:
Update Divide_stat
Set divide_stat.new_amount= (select Divide_act_channel_day.new_amount from Divide_act_channel_day
whereDivide_stat.day=divide_act_channel_day.log_time
and Divide_stat.channel=divide_act_channel_day.channel
);

SQL Error: ORA-01427: single-line subquery returns multiple rows
01427.00000-"Single-row subquery returns more than one row"


--presumably there is a case in the subquery that returns multiple rows, try adding rownum<2 to the subquery, that is, restricting the return of one row of data. Success!
Update Divide_stat
Set divide_stat.new_amount= (select Divide_act_channel_day.new_amount from Divide_act_channel_day
where Divide_stat.day=divide_act_channel_day.log_time
and Divide_stat.channel=divide_act_channel_day.channel and ROWNUM&LT;2);


--Find the duplicate rows of the Divide_act_channel_day table. There are 9 rows to repeat.
SELECT * FROM
(
Select COUNT (*) Total,log_time,channel from Divide_act_channel_day
Group BY Log_time, channel
)
where total>1;

Total Log_time CHANNEL
---------------------- ------------------------- --------------------------------------------------
2 2012-12-12 00:00:00 0
2 2012-12-13 00:00:00 0
2 2013-01-07 00:00:00 0
2 2012-12-15 00:00:00 0
2 2012-12-01 00:00:00 0
2 2012-12-31 00:00:00 0
2 2012-12-04 00:00:00 0
2 2012-12-23 00:00:00 0
2 2012-12-21 00:00:00 0

9 Selected rows


--Observe the Divide_act_channel_day table and find that it has no duplicate rows at all. It appears to be a row duplication where the precision of the where condition is insufficient.

--Observe the two tables of Divide_act_channel_day and Divide_stat, and find that they also have columns that can be associated: AMOUNT and New_user_amount.
-so there is no repetition of the line.
SELECT * FROM
(
Select COUNT (*) Total,log_time,channel,Amount,new_user_amountFrom Divide_act_channel_day
Group by Log_time, channel,Amount, New_user_amount
)
where total>1;

No rows selected



--Modify the Upadte statement
Update Divide_stat
Set divide_stat.new_amount= (select Divide_act_channel_day.new_amount from Divide_act_channel_day
where Divide_stat.day=divide_act_channel_day.log_time
and Divide_stat.channel=divide_act_channel_day.channel andDivide_stat.amount=divide_act_channel_day.amount
and Divide_stat. New_user_amount=divide_act_channel_day. New_user_amount);


Conclusion:
1. In order to update a column of table B according to a column of a, it is important to find out all the fields that can be associated with a B table, so that the "ORA-01427: Single row subquery returns multiple rows" is essentially not present;
2. If there is a duplicate line in table A, then add the rownum<2 condition to solve it.

occal [Problem Resolution]ora-01427: Single-line subquery returns multiple rows

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.