Mulgard Mulgard - 28 days ago 11
SQL Question

Scalar subquery contains more than one row

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.

Answer

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).