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

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

694

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

Total Answers 1
33

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

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

Top rated topics

How to get latitude and longitude separately in 2 textviews in android studio JAVA?

Update ideavim: remap <ctrl+j> for <b> and <ctrl+l> for <w> not working

How to get response from blockchain.com by using wss in Python?

Add a touch-event listener to a Xamarin custom control using Bindable Property

How to return to the same condition if the password is wrong twice

Could not create listener with electron.ipcRenderer.on

It is not printing from Child Class

Arduino+Ethernet shield. It does not connect to a server by local name, but do connect by the IP

Centering a dcc.input field in Plotly Dash

How can I give names to output files which are similar to the name of the SAS script being executed?

Why do i keep getting 'Error [ERR_HTTP_HEADERS_SENT]: Cannot set headers after they are sent to the client' error?

Google Cloud platform: Nifi UI doesn't works on External Address

How to I import an array (in js formating) from a json file?

How to show an object inside of another object as a normal text or a string?

How to use multiple files in rust?

Uncaught TypeError cannot read properties of undefined

Where is Vue 3 versions list?

Oracle Hierarchy to get all children and all parents of each id

C# - Unit testing - initializing private fields - ASP.NET Core 5 - NUnit test

How to connect to Tableau Online using Google Apps Script?

Django How to pass variable from a view to a form view

How can I find the longest contiguous subsequence in a rising sequence in Python?

Why doesn't my setInterval function run more than once?

Redirect HTTP to HTTPS .htaccess

GroupBy with ApplyInPandas in PySpark - how to implement a UDF correctly?

Ovs-ofctl add a flow to allow only ARP ethernet frames

How to unit test a function inside a Higher Order Function in JavaScript?

Background opacity from rgba applying to inner class

Programmatic way of checking for Windows updates

Android String value from Preferences not change depending on language set in phone

Spark-shell throws java.lang.reflect.InvocationTargetException on running

Error with using @ComponentScan on multiple packages in Spring Boot

My code for creating rooms out of voxels isn't working; rooms keep colliding? Code included

MASM 32-bit 2d arrays accessing with conditional control flow directives error A2029:multiple base registers not allowed

Is anyone having problems in tailwindcss installed via npm?

I need help finishing my connect4 module discordjs

Plt.violinplot: Adjusting the length of the horizontal min and max lines

Rails app with puma generating several errors on postgres

Blender 9.3 and higher: viewport shading color switches suddenly to black

How to display an image in real-time using Rust?

How can I sort and concat a csv file in a dataframe

How to add a specific element from an object array to session Storage in JavaScript

Altair Scatter Plot tickMinStep

Having issue to load Image in webview above version 28

Using pluto.jl remotely

Pyinstaller Codec Error on Executable When Using No Console And No Debug Option

Raise self.model.DoesNotExist( users.models.Profile.DoesNotExist: Profile matching query does not exist

I am not sure I understand how pool based allocators deallocate

Laravel authentication not working npm install&&npm run dev

Transform Word Document data into showing the "new line" separator within the text

Top