BigQuery - Transaction rows double when Insert Into and Left Outer Join statement used

Question

I'm trying to update 5 columns in a table with last year values by doing a left outer join on same table. And then I'm updating a 'Text' column in it. I'm using the below code but it is doubling the no. of transaction rows although no data difference among the replicated rows:

insert into table2
select coalesce(a.F_YEAR,b.F_YEAR+1) F_YEAR, 
coalesce(a.POSTING_DATE,b.POSTING_DATE+366)POSTING_DATE,
coalesce(a.Material,b.Material),
coalesce(a.R_no,b.R_no),
coalesce(a.Text,b.Text),
a.Amt,
a.X_BUDGET,
a.Y_BUDGET,
a.X_FORECAST,
a.Y_FORECAST
from table2 a
Left outer join table2 b 
on a.F_YEAR-1 = b.F_YEAR
and extract(month from a.POSTING_DATE) = extract(month from b.POSTING_DATE)
and a.R_no = b.R_no
and a.MATERIAL = b.MATERIAL
and a.TEXT=b.TEXT;

select *
from table2
where F_Year <= extract(year from current_date('Asia/India'));

update table2 tgt
set tgt.TEXT=b.TEXT
from table3 b
where
tgt.R_no=b.G_L_no,'0' and tgt.TEXT is null;

How can I get the right output without the double no. of rows ?

Answer 1

Probably the duplication happens in the first step insert into table2. New data is being inserted into the original table. Try inserting into some new table instead insert into table_new