Welcome To TechBrothersIT: Snowflake SQL Tutorial 117 - UPDATE Has A Column That Says Your Join Matched Twice

Snowflake SQL Tutorial 117 - UPDATE Has A Column That Says Your Join Matched Twice

Snowflake SQL · Episode 117

In this post we are going to cover the UPDATE statement in Snowflake, and one column in its report that almost nobody reads.

UPDATE changes values in rows that are already there. The report it gives back has two numbers in it, and the second one is called number of multi-joined rows updated. That is Snowflake telling you that one of your rows matched more than one row in your source, and it had to pick a value.

We will start with the simplest UPDATE there is and build it up to the one you write at work, which is an UPDATE with a FROM clause. Then we will break it on purpose and read what the report says.

Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

Episode 117 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's start with the simplest UPDATE
Now the one everybody does by accident
What about an UPDATE that matches nothing?
And a value the column cannot hold?
Now the one you write at work: UPDATE ... FROM
Now let's try a dirty price list
Before we run it, let's read the parameter
Now the same statement over the dirty list
Let's turn that warning into a refusal
Does that parameter block UPDATE ... FROM altogether?
Let's put the parameter back
One last thing: four lines that say whether a source is safe
And putting the schema back

Let's create some sample data

First, where the session is pointing.

Query

SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_VERSION()   AS THE_VERSION;

Result1 row · 3 columns

THE_DATABASE	THE_SCHEMA	THE_VERSION
DEMO_DB	PUBLIC	10.35.101

Now a small stock table. A product code, a name, a price, how many we have, and the date the row was last touched.

Sample data

-- Sample data for this post.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE TABLE STOCK_T117 (
    SKU      VARCHAR(10),
    NAME     VARCHAR(30),
    PRICE    NUMBER(10,2),
    IN_STOCK INT,
    UPDATED  DATE
);

INSERT INTO STOCK_T117 (SKU, NAME, PRICE, IN_STOCK, UPDATED)
SELECT 'A-1', 'Anvil',    19.99, 10, '2026-01-01'::DATE UNION ALL
SELECT 'B-2', 'Bellows',  34.50,  4, '2026-01-01'::DATE UNION ALL
SELECT 'C-3', 'Crucible', 88.00,  0, '2026-01-01'::DATE UNION ALL
SELECT 'D-4', 'Drill',    12.25,  7, '2026-01-01'::DATE;

Query

SELECT SKU, NAME, PRICE, IN_STOCK, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 5 columns

SKU	NAME	PRICE	IN_STOCK	UPDATED
A-1	Anvil	19.99	10	2026-01-01
B-2	Bellows	34.50	4	2026-01-01
C-3	Crucible	88.00	0	2026-01-01
D-4	Drill	12.25	7	2026-01-01

Four products, and all four were last updated on the same day. That date column is how we will tell which rows a statement touched.

Let's start with the simplest UPDATE

One row, found by its key. SET the columns we want to change, then WHERE to say which row.

Query

UPDATE STOCK_T117 SET PRICE = 21.99, UPDATED = '2026-04-01'::DATE
WHERE SKU = 'A-1';

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
1	0

Now look at that report properly, because it has two columns. number of rows updated says one, which is the number everybody reads. And number of multi-joined rows updated says zero.

Hold on to that second column. It is the whole post.

Query

SELECT SKU, NAME, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 4 columns

SKU	NAME	PRICE	UPDATED
A-1	Anvil	21.99	2026-04-01
B-2	Bellows	34.50	2026-01-01
C-3	Crucible	88.00	2026-01-01
D-4	Drill	12.25	2026-01-01

The price moved and the date moved, and only on that one row. The other three are exactly as they were.

Now the one everybody does by accident

An UPDATE with no WHERE clause at all.

Query

UPDATE STOCK_T117 SET UPDATED = '2026-04-02'::DATE;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
4	0

Four rows updated, which is every row in the table.

Query

SELECT SKU, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 3 columns

SKU	PRICE	UPDATED
A-1	21.99	2026-04-02
B-2	34.50	2026-04-02
C-3	88.00	2026-04-02
D-4	12.25	2026-04-02

Let's count that instead of reading it off the grid.

Query

SELECT COUNT(*) AS ROWS_TOTAL,
       COUNT(DISTINCT UPDATED) AS DISTINCT_UPDATED_DATES
FROM STOCK_T117;

Result1 row · 2 columns

ROWS_TOTAL	DISTINCT_UPDATED_DATES
4	1

Four rows, and exactly one distinct date between them. They all moved.

A habit worth having

Write the WHERE clause first and the SET clause second. An UPDATE with no WHERE clause is a legal statement and Snowflake will not ask you whether you meant it.

What about an UPDATE that matches nothing?

Let's point the WHERE clause at a key that does not exist.

Query

UPDATE STOCK_T117 SET PRICE = 0 WHERE SKU = 'NO-SUCH-SKU';

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
0	0

Zero and zero, and it is not an error. The same shape of answer we got from an INSERT that matched nothing in Episode 115.

And a value the column cannot hold?

PRICE is a NUMBER(10,2), so it has room for a certain size of number and no more. Let's go past it.

Query

UPDATE STOCK_T117 SET PRICE = 99999999999999 WHERE SKU = 'B-2';

Query failed — Snowflake returned an error

100046 (22003): DML operation to table STOCK_T117 failed on column PRICE with error: Number out of representable range: type FIXED[SB8](10,2){nullable}, value 99999999999999

Refused. Number out of representable range, and it names the type it could not fit the value into.

Query

SELECT SKU, PRICE FROM STOCK_T117 WHERE SKU = 'B-2';

Result1 row · 2 columns

SKU	PRICE
B-2	34.50

The price is exactly as it was. A refused UPDATE changes nothing.

Now the one you write at work: UPDATE ... FROM

At work you do not type the new values in. They arrive in another table and you join to it. Let's make a price list with one row per product code, which is the important part.

Query

CREATE OR REPLACE TABLE PRICES_CLEAN_T117 (
    SKU       VARCHAR(10),
    NEW_PRICE NUMBER(10,2)
);

Result1 row · 1 column

status
Table PRICES_CLEAN_T117 successfully created.

Query

INSERT INTO PRICES_CLEAN_T117 (SKU, NEW_PRICE)
SELECT 'A-1', 25.00
UNION ALL SELECT 'B-2', 30.00
UNION ALL SELECT 'C-3', 90.00;

Result1 row · 1 column

number of rows inserted
3

Query

SELECT SKU, NEW_PRICE FROM PRICES_CLEAN_T117 ORDER BY SKU;

Result3 rows · 2 columns

SKU	NEW_PRICE
A-1	25.00
B-2	30.00
C-3	90.00

Three rows, one each for the first three products. Our stock table has four.

Now the update. SET the price from the list, FROM the list, and a WHERE clause to match the codes up.

Query

UPDATE STOCK_T117 AS S
SET PRICE = P.NEW_PRICE, UPDATED = '2026-04-03'::DATE
FROM PRICES_CLEAN_T117 AS P
WHERE P.SKU = S.SKU;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
3	0

Three rows updated, and zero multi-joined. Clean.

Query

SELECT SKU, NAME, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 4 columns

SKU	NAME	PRICE	UPDATED
A-1	Anvil	25.00	2026-04-03
B-2	Bellows	30.00	2026-04-03
C-3	Crucible	90.00	2026-04-03
D-4	Drill	12.25	2026-04-02

The three prices moved and so did their dates. But look at the fourth row.

Query

SELECT SKU, PRICE, UPDATED FROM STOCK_T117 WHERE SKU = 'D-4';

Result1 row · 3 columns

SKU	PRICE	UPDATED
D-4	12.25	2026-04-02

The old price and the old date. That product was not in the price list, so nothing touched it.

UPDATE ... FROM is an inner join. A row in the target with no match in the source is left alone, with no error, no warning, and no mention of it anywhere in the report. Which is correct behaviour, and is also exactly how a partial load looks like a full one.

Now let's try a dirty price list

Here is the kind of list you get when two feeds are stacked on top of each other. It has a source column so we can see where each row came from.

Query

CREATE OR REPLACE TABLE PRICES_DIRTY_T117 (
    SKU       VARCHAR(10),
    NEW_PRICE NUMBER(10,2),
    SOURCE    VARCHAR(20)
);

Result1 row · 1 column

status
Table PRICES_DIRTY_T117 successfully created.

Query

INSERT INTO PRICES_DIRTY_T117 (SKU, NEW_PRICE, SOURCE)
SELECT 'A-1', 100.00, 'feed-one'
UNION ALL SELECT 'A-1', 200.00, 'feed-two'
UNION ALL SELECT 'B-2',  50.00, 'feed-one';

Result1 row · 1 column

number of rows inserted
3

Query

SELECT SKU, NEW_PRICE, SOURCE FROM PRICES_DIRTY_T117 ORDER BY SKU, NEW_PRICE;

Result3 rows · 3 columns

SKU	NEW_PRICE	SOURCE
A-1	100.00	feed-one
A-1	200.00	feed-two
B-2	50.00	feed-one

Three rows, and the first product appears twice with two different prices. Let's count that rather than spot it by eye.

Query

SELECT SKU, COUNT(*) AS ROWS_FOR_THIS_SKU
FROM PRICES_DIRTY_T117 GROUP BY SKU ORDER BY SKU;

Result2 rows · 2 columns

SKU	ROWS_FOR_THIS_SKU
A-1	2
B-2	1

So one row in our stock table has two candidate prices waiting for it, and the UPDATE is going to have to pick one of them.

Before we run it, let's read the parameter

There is a session parameter that decides whether that case is an error or not. Let's read it rather than guess at it.

Query

SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_UPDATE' IN SESSION;

Result1 row · 6 columns · scroll →

key	value	default	level	description	type
ERROR_ON_NONDETERMINISTIC_UPDATE	false	false		raise an error when attempting to update a row that joins many rows	BOOLEAN

Six columns, and the description column says what it is for in Snowflake's own words. Let's keep the four that matter.

Query

SELECT "key", "value", "default", "level"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 4 columns

key	value	default	level
ERROR_ON_NONDETERMINISTIC_UPDATE	false	false

value is false and default is false. So it is off, out of the box, and nobody turned it off.

The level cell is empty rather than reading NULL, and that is not a missing value. A SHOW command reports nothing-set-here as an empty string, and the level is blank because the parameter has never been set at any level. It will have something in it shortly.

Now the same statement over the dirty list

Here are the prices as they stand.

Query

SELECT SKU, PRICE FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 2 columns

SKU	PRICE
A-1	25.00
B-2	30.00
C-3	90.00
D-4	12.25

And here is the update. Nothing about the statement has changed from the clean run. Only the table it reads from.

Query

UPDATE STOCK_T117 AS S
SET PRICE = P.NEW_PRICE, UPDATED = '2026-04-04'::DATE
FROM PRICES_DIRTY_T117 AS P
WHERE P.SKU = S.SKU;

Result1 row · 2 columns

number of rows updated	number of multi-joined rows updated
2	1

Two rows updated. And number of multi-joined rows updated says one.

There it is. That is Snowflake telling you what just happened. The statement succeeded. Nothing failed. And it still told you.

Query

SELECT SKU, NAME, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 4 columns

SKU	NAME	PRICE	UPDATED
A-1	Anvil	100.00	2026-04-04
B-2	Bellows	50.00	2026-04-04
C-3	Crucible	90.00	2026-04-03
D-4	Drill	12.25	2026-04-02

Let's put the price that landed next to the two that were offered.

Query

SELECT (SELECT PRICE FROM STOCK_T117 WHERE SKU = 'A-1') AS THE_PRICE_NOW,
       (SELECT MIN(NEW_PRICE) FROM PRICES_DIRTY_T117 WHERE SKU = 'A-1')
                                                          AS CANDIDATE_ONE,
       (SELECT MAX(NEW_PRICE) FROM PRICES_DIRTY_T117 WHERE SKU = 'A-1')
                                                          AS CANDIDATE_TWO;

Result1 row · 3 columns

THE_PRICE_NOW	CANDIDATE_ONE	CANDIDATE_TWO
100.00	100.00	200.00

What this post will not claim

In this run the lower of the two candidates landed. I am not going to tell you which one wins, because I have seen one run, and one run is not a rule. The point is that Snowflake had to choose at all, and that it said so in a column rather than in a warning. If your pipeline depends on which value arrives, your pipeline depends on luck.

Let's turn that warning into a refusal

Now let's set the parameter and run the identical statement again.

Query

ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_UPDATE = TRUE;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_UPDATE' IN SESSION;

Result1 row · 6 columns · scroll →

key	value	default	level	description	type
ERROR_ON_NONDETERMINISTIC_UPDATE	true	false	SESSION	raise an error when attempting to update a row that joins many rows	BOOLEAN

Query

SELECT "key", "value", "level"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

key	value	level
ERROR_ON_NONDETERMINISTIC_UPDATE	true	SESSION

value is true now, and the level cell that was empty a moment ago says SESSION. That is where the setting lives.

Same statement, same dirty list.

Query

UPDATE STOCK_T117 AS S
SET PRICE = P.NEW_PRICE, UPDATED = '2026-04-05'::DATE
FROM PRICES_DIRTY_T117 AS P
WHERE P.SKU = S.SKU;

Query failed — Snowflake returned an error

100090 (42P18): Duplicate row detected during DML action
Row Values: ["A-1", "Anvil", 10000, 10, 20547]

Refused. Duplicate row detected during DML action, and then it hands you the row that caused it.

Those values are worth a second look, because they are not written the way you would write them. The price is there without its decimal point, because a NUMBER(10,2) is held as a whole number and scaled. The last value is the date, held as a day count. That is real output in Snowflake's own internal encoding.

Query

SELECT SKU, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 3 columns

SKU	PRICE	UPDATED
A-1	100.00	2026-04-04
B-2	50.00	2026-04-04
C-3	90.00	2026-04-03
D-4	12.25	2026-04-02

And the prices did not move. The refusal rolled the whole statement back, including the row that was not ambiguous.

Does that parameter block UPDATE ... FROM altogether?

No, and this is worth checking rather than assuming. Let's run the clean list again with the parameter still on.

Query

UPDATE STOCK_T117 AS S
SET PRICE = P.NEW_PRICE, UPDATED = '2026-04-06'::DATE
FROM PRICES_CLEAN_T117 AS P
WHERE P.SKU = S.SKU;

Result1 row · 1 column

number of rows updated
3

Query

SELECT SKU, PRICE, UPDATED FROM STOCK_T117 ORDER BY SKU;

Result4 rows · 3 columns

SKU	PRICE	UPDATED
A-1	25.00	2026-04-06
B-2	30.00	2026-04-06
C-3	90.00	2026-04-06
D-4	12.25	2026-04-02

Three rows updated, no complaint. So it refuses ambiguity, not UPDATE ... FROM. A source with one row per key is as welcome as it ever was.

The second column is not always there

Have a look at the shape of that report. It has one column, where every other successful UPDATE on this page has two. With the parameter on, Snowflake does not report number of multi-joined rows updated at all. That makes sense when you think about it. A value above zero in that column would have been the error we just saw, so the column has nothing left to say. The second column arrives on almost every UPDATE rather than on all of them, and the one place it goes missing is the one place it could not have told you anything.

Let's put the parameter back

UNSET returns it to the default rather than setting it to false by hand, which is a different thing.

Query

ALTER SESSION UNSET ERROR_ON_NONDETERMINISTIC_UPDATE;

Result1 row · 1 column

status
Statement executed successfully.

Query

SHOW PARAMETERS LIKE 'ERROR_ON_NONDETERMINISTIC_UPDATE' IN SESSION;

Result1 row · 6 columns · scroll →

key	value	default	level	description	type
ERROR_ON_NONDETERMINISTIC_UPDATE	false	false		raise an error when attempting to update a row that joins many rows	BOOLEAN

Query

SELECT "key", "value", "level"
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 3 columns

key	value	level
ERROR_ON_NONDETERMINISTIC_UPDATE	false

Back to false, and the level cell is empty again. Not set at any level, which is where we found it.

One last thing: four lines that say whether a source is safe

All of this comes down to one question about the source table: does it have one row per key? Count the rows, count the distinct keys, and subtract. Here it is on the dirty list.

Query

SELECT COUNT(*)                                   AS ROWS_IN_THE_SOURCE,
       COUNT(DISTINCT SKU)                        AS DISTINCT_KEYS,
       COUNT(*) - COUNT(DISTINCT SKU)             AS DUPLICATE_KEYS,
       IFF(COUNT(*) = COUNT(DISTINCT SKU), 'SAFE', 'AMBIGUOUS') AS THE_VERDICT
FROM PRICES_DIRTY_T117;

Result1 row · 4 columns

ROWS_IN_THE_SOURCE	DISTINCT_KEYS	DUPLICATE_KEYS	THE_VERDICT
3	2	1	AMBIGUOUS

Three rows, two distinct keys, so one duplicate. The verdict column says AMBIGUOUS.

And the same four lines on the clean list.

Query

SELECT COUNT(*)                                   AS ROWS_IN_THE_SOURCE,
       COUNT(DISTINCT SKU)                        AS DISTINCT_KEYS,
       COUNT(*) - COUNT(DISTINCT SKU)             AS DUPLICATE_KEYS,
       IFF(COUNT(*) = COUNT(DISTINCT SKU), 'SAFE', 'AMBIGUOUS') AS THE_VERDICT
FROM PRICES_CLEAN_T117;

Result1 row · 4 columns

ROWS_IN_THE_SOURCE	DISTINCT_KEYS	DUPLICATE_KEYS	THE_VERDICT
3	3	0	SAFE

Three rows, three distinct keys, no duplicates, SAFE. Run that before an UPDATE ... FROM and you never have to wonder afterwards.

And putting the schema back

Three tables were made for this post, so three tables go.

Query

DROP TABLE IF EXISTS STOCK_T117;

Result1 row · 1 column

status
STOCK_T117 successfully dropped.

Query

DROP TABLE IF EXISTS PRICES_CLEAN_T117;

Result1 row · 1 column

status
PRICES_CLEAN_T117 successfully dropped.

Query

DROP TABLE IF EXISTS PRICES_DIRTY_T117;

Result1 row · 1 column

status
PRICES_DIRTY_T117 successfully dropped.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T117') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 4 columns

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	10

No probe tables left, and the rest of the database still reads fine.

Key points

An UPDATE reports number of rows updated and number of multi-joined rows updated. The second one is the warning, and it arrives in the report of a statement that succeeded.
That second column is zero when the source has one row per key, and counts the target rows that matched more than one source row when it has not.
With ERROR_ON_NONDETERMINISTIC_UPDATE on, the second column is not reported at all, because a value above zero would have been an error instead.
An UPDATE with no WHERE clause moves every row, and an UPDATE that matches nothing reports zero and is not an error.
A value the column cannot hold is refused, with the type named, and nothing changes.
UPDATE ... FROM is an inner join. A target row with no match is left alone and is never mentioned in the report.
ERROR_ON_NONDETERMINISTIC_UPDATE is false by default. Turn it on and the ambiguous statement is refused with Duplicate row detected during DML action.
The refusal rolls the whole statement back, and it prints the offending row in Snowflake's internal encoding rather than yours.
It refuses ambiguity, not UPDATE ... FROM. A clean source still runs with the parameter on.
Count the rows, count the distinct keys, subtract. Do that before any UPDATE ... FROM.

Next in this series

Episode 118 — DELETE. The same story from the other side, including the join you forgot to write and what it takes out of the table when you forget it.

Every query, every result grid and every error on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The three _T117 tables are created and dropped by the script and the products and prices in them are made up for teaching. Empty cells marked empty are empty strings from SHOW output, not nulls. Nothing on this page is timed and no performance claims are made anywhere in this series. UPDATE behaviour follows the Snowflake UPDATE documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 117 - UPDATE Has A Column That Says Your Join Matched Twice

Let's create some sample data

Let's start with the simplest UPDATE

Now the one everybody does by accident

What about an UPDATE that matches nothing?

And a value the column cannot hold?

Now the one you write at work: UPDATE ... FROM

Now let's try a dirty price list

Before we run it, let's read the parameter

Now the same statement over the dirty list

Let's turn that warning into a refusal

Does that parameter block UPDATE ... FROM altogether?

Let's put the parameter back

One last thing: four lines that say whether a source is safe

And putting the schema back

Key points

No comments:

Post a Comment