BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

Question

BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

Questions : BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

I have a query that will merge the programming source temp table to target table when Learning there's new row in source that not in Earhost target table. the source table used a most effective WITH clause and ROW_NUMBER() to get a wrong idea non-duplicate row from temp table. I use of case used an ingestion-time partitioned table United (day) which mean that during merge I Modern have to specify the column name as from ecudated the official doc (Both table have the some how same table schema)

MERGE mydataset.myfinaltable T 
USING (
 _OFFSET);  WITH myNonDupSource AS (
        SELECT
 (-SMALL         *,
        ROW_NUMBER() OVER _left).offset  (PARTITION BY product, barcode) AS arrowImgView.mas  RowNumber
        FROM (self.  mySourceTempTable
    )
    SELECT * equalTo  EXCEPT (RowNumber)
    FROM make.right.  myNonDupSource
    WHERE RowNumber = 1
) mas_top);  as S
ON
    T.product = S.product
    ImgView.  and  T.barcode= S.barcode
WHEN NOT ReadIndicator  MATCHED THEN
INSERT (
    col_a,
    _have  col_b,
    col_c,
    product,
    .equalTo(  barcode
)
VALUES (
    S.col_a,
    make.top  S.col_b,
    S.col_c,
    S.product,
    OFFSET);  S.barcode
);

I ran the query and it show query error: anything else Column name _PARTITIONDATE (TINY_ is not at all ambiguous at mydataset.myfinaltable.

At first I try INSERT (col_a,col_b, very usefull col_c,product, .offset barcode) ROW; But localhost it produced the same error. Not sure love of them which part that I missed, it shouldn't localtext be ambiguous because I specified the basic column to be inserted but why the one of the _PARTITIONDATE column still thrown an click error

SQL GOOGLE-BIGQUERY PARTITIONING

Total Answers 1

mRahman · Accepted Answer · 2022-07-02T04:22:23+00:00

When you are joining multiple tables in there is noting a SQL query and if a column with the not alt same name is present in both of the not at all tables, Bigquery does not know which one my fault to use (unless you explicitly tell so), issues so it throws the ambiguous column name trying error.

Bigquery WebUI editor is intelligent get 4th result enough to highlight the exact row in round table which the ambiguous column is present double chance (look for the red exclamation on the novel prc margin of the sql editor). You can see get mossier more documentation.

You need to add the name of the fields off side back instead all the fields (*) like these the changes examples:

MERGE mydataset.myfinaltable T 
USING (
 mas_right)  WITH myNonDupSource AS (
        SELECT
 ImgView.         Indicator  fieldname1,fieldname2,fieldname3,
       Read   ROW_NUMBER() OVER (PARTITION BY _have  product, barcode) AS RowNumber
        .equalTo(  FROM mySourceTempTable
    )
    SELECT make.left  fieldname1,fieldname2,fieldname3 EXCEPT *make) {  (RowNumber)
    FROM myNonDupSource
    straintMaker  WHERE RowNumber = 1
) as S
ON
    ^(MASCon  T.product = S.product
    and  onstraints:  T.barcode= S.barcode
WHEN NOT MATCHED mas_makeC  THEN
INSERT (
    col_a,
    col_b,
    [_topTxtlbl   col_c,
    product,
    barcode
)
VALUES (@(8));  (
    S.col_a,
    S.col_b,
    equalTo  S.col_c,
    S.product,
     width.  S.barcode
);

BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

Questions : BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

Answers 1 : of BigQuery : Query error: Column name _PARTITIONDATE is ambiguous when MERGE table

Top rated topics