SQL equivalent of MS Excel's VLOOKUP + Replace

Transact-SQL
1

Hello, I'm pretty new and trying to learn how to use SQL through Google's BigQuery platform and I'm currently trying to figure out MS Excel's equivalent of VLOOKUP + REPLACE and I found a previous topic on the old forum (topic ID=196441), I tried using it but I get an error saying that the FROM clause was already defined as the UPDATE target.

TLDR of the topic on the old forum below:

Fact table:
ID Name Desc Region
1 a xxxx UK
2 b yyyy US
3 c zzzz US
4 d aaaa China

Dimension table:
Region_ID Region_Name
1 US
2 UK
3 China
4 Japan

Does a look-up check and replace the region_name in the fact table by using the region_ID

UPDATE F
SET Region = D.Region_ID
FROM Fact F
INNER JOIN Dimension D ON F.Region = D.Region_Name

2

Please, post DDL for CREATE and INSERT in order to study the structure of the tables and have a minimum of data to work with.

From a first look there seems to be some type inconsistencies in the query.

For example: SET Region = D.Region_ID

From your example it would look like Region is a varchar and Region_ID is an integer.

Provide DDL to clarify.

1 Like
3

UPDATE Fact
SET Name='q'
WHERE Country='UK'

Look-up check is done by WHERE operator and Replace is done by UPDATE. No need to complicate.