当前位置: 动力学知识库 > 问答 > 编程问答 >

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

网友答案:

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

网友答案:

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)
网友答案:

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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: