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