CODEWITHSUNDEEP
google-bigquery
alex
alex

Reputation: 1917

Replace column in one table with a column with the same name from another table

I'm trying to replace a column in one table with a column with the same name from another table

the below query gives an error Column item_name in SELECT * REPLACE list is ambiguous at [6:38]

WITH items AS (
    SELECT 1 as item_id, "sprocket" as item_name),
orders AS (
    SELECT 123 as order_id, 1 as item_id, "ff" as item_name)

SELECT * REPLACE (items.item_name AS item_name)
FROM orders
JOIN items on orders.item_id  = items.item_id

when i try to resolve ambiguity by changing (items.item_name AS item_name) to (items.item_name AS orders.item_name) it throws a different error: Syntax error: Expected ")" or "," but got "." at [6:44]

SELECT * REPLACE (items.item_name AS orders.item_name)
    FROM orders
    JOIN items on orders.item_id  = items.item_id

the workaround of changing the name of the column to be replaced from item_name to item_name_1 in one of the tables works but how come it fails on standard table.column syntax inside REPLACE

WITH items AS (
    SELECT 1 as item_id, "sprocket" as item_name),
orders AS (
    SELECT 123 as order_id, 1 as item_id, "ff" as item_name_1)

SELECT * REPLACE (items.item_name AS item_name_1)
FROM orders
JOIN items on orders.item_id  = items.item_id

Upvotes: 0

Views: 709

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Use below

WITH items AS (
    SELECT 1 as item_id, "sprocket" as item_name),
orders AS (
    SELECT 123 as order_id, 1 as item_id, "ff" as item_name)

SELECT orders.* REPLACE (items.item_name AS item_name)
FROM orders
JOIN items on orders.item_id  = items.item_id

Upvotes: 1

Related Questions