0

We must need to use Legacy SQL in BigQuery. But, Merge is not working in Legacy SQL. How we write below query in Legacy SQL?

MERGE [ABC:xyz.tmp_cards] AS target_tbl 
USING [ABC:xyz.tmp_cards_1533188902] AS source_tbl 
ON target_tbl.id = source_tbl.id
WHEN MATCHED AND target_tbl.id = source_tbl.id THEN
   UPDATE SET target_tbl.id = source_tbl.id,
   target_tbl.user_id = source_tbl.user_id,
   target_tbl.expiration_date = source_tbl.expiration_date,
   target_tbl.created_at = source_tbl.created_at,
   target_tbl.updated_at = source_tbl.updated_at 
WHEN NOT MATCHED THEN 
   INSERT (id, user_id, expiration_date, created_at, updated_at) 
   VALUES (source_tbl.id, source_tbl.user_id, source_tbl.expiration_date, source_tbl.created_at, source_tbl.updated_at)
Jimesh Gajera
  • 612
  • 1
  • 11
  • 32

1 Answers1

1

Support for DML MERGE statements appeared in Beta just this year for standard SQL. It's not possible to do it in Legacy SQL and this is why Standard SQL is the preferred SQL dialect for querying data stored in BigQuery. Because the new features are for the last DML for BigQuery and not the old one.

Glorfindel
  • 21,988
  • 13
  • 81
  • 109
Temu
  • 859
  • 4
  • 11