Scalar subquery contains more than one row

Question

Im working with H2 database and wanted to move some data. For that I created the following Query:

UPDATE CUSTOMER
SET EMAIL = SELECT service.EMAIL
FROM CUSTOMER_SERVICE AS service
INNER JOIN CUSTOMER AS customer ON service.ID = customer.CUSTOMER_SERVICE_ID;

When I now perform it in the H2 console I get the following error:

Scalar subquery contains more than one row; SQL statement:

UPDATE CUSTOMER
SET EMAIL = SELECT service.EMAIL
FROM CUSTOMER_SERVICE AS service
INNER JOIN CUSTOMER AS customer ON service.ID = customer.CUSTOMER_SERVICE_ID [90053-192] 90053/90053 (Hilfe)

What is this error telling me?

EDIT

What I want to achiev with my query:

Actually every CUSTOMER has a CUSTOMER_SERVICE. And I simply want to move the COLUMN EMAIL from CUSTOMER_SERVICE to the CUSTOMER Table. for that I already added a email column to the user. I hoped to be able to do it with my query but obviously not.

Bricklaying

Answer 1

Your query is not syntactically valid (all subqueries must have parentheses around them).

What you are missing is a correlation clause. I believe you want:

UPDATE CUSTOMER c
    SET EMAIL = (SELECT cs.EMAIL
                 FROM CUSTOMER_SERVICE s
                 WHERE s.ID = c.CUSTOMER_SERVICE_ID
                );

I don't know what this is supposed to be: [90053-192] 90053/90053 (Hilfe).

Streamliner

[90053-192] 90053/90053 (Hilfe) is H2's error code (h2database.com/javadoc/org/h2/api/ErrorCode.html#c90053): SCALAR_SUBQUERY_CONTAINS_MORE_THAN_ONE_ROW (The error with code 90053 is thrown when a subquery that is used as a value contains more than one row). – Saltine

Answer 2

Your select query is returning more than one row. If you don't want it to, then you need to do something like an aggregate or LIMIT 1 or something similar.

Loring

But I want taht query to perform on every Customer that I have. – Bricklaying

Answer 3

Your sub-query for at least one of your customers has multiple email addresses.

You could ... (Select top 1 serverice.email ... Or ... (Select max(serverice.email) ...

Update Customer Set EMail=B.Email
 From  Customer A
 Join  (Select ID,max(EMail) as EMail From CUSTOMER_SERVICE Group By ID) B
   on  (A.CUSTOMER_SERVICE_ID = B.ID)

Kaffir

@Bricklaying BINGO! – Kaffir

@Bricklaying My first choice would be the max() option – Kaffir

@Bricklaying If multiple emails, do you want to see them as a DISTINCT delimited string? – Kaffir

@Bricklaying If multiple distinct emails, do you want the last one entered? – Kaffir

@Bricklaying See updated answer ... Full Update Statement – Kaffir

@Bricklaying See update. A sub-query/join may be more efficient – Kaffir

Syntax Fehler in SQL Befehl "UPDATE Customer SET EMAIL=B.EMAIL FROM[*] Customer A JOIN (SELECT ID,MAX(EMAIL) AS EMAIL FROM CUSTOMER_SERVICE GROUP BY ID) B ON (A.CUSTOMER_SERVICE_ID = B.ID) " – Bricklaying

What is [*] after the FROM? – Kaffir

I have no idea. I guess it says that FROM is causing the syntax error but actually your query is valid SQL. – Bricklaying

oh I found the syntax error. too easy so that we could not see it. there is a SELECT missing between FROM and Customer :) Thank you. – Bricklaying

Answer 4

I've spend a lot time with this error type (there shoudn't be duplicates in DB).
At last I've found problem via SQL with COUNT like this:

UPDATE CUSTOMER
SET EMAIL = SELECT COUNT(service.EMAIL)
FROM CUSTOMER_SERVICE AS service
INNER JOIN CUSTOMER AS customer ON service.ID = 
customer.CUSTOMER_SERVICE_ID;

And then select problem rows with EMAIL!='1'

Outofdoor