MySQL Error Message: Subquery Returns More than 1 Row

Question

Would you help me to fix this, please:

SELECT
  (SELECT AES_DECRYPT(cryptoword, SHA2('DatabaseEncryption1', 512)) FROM file_tree) AS cryptoword1,
  (SELECT AES_DECRYPT(name, SHA2(cryptoword1, 512)) FROM file_tree) AS name;

As the topic says, I get error saying that my subquery returns more than 1 row. What I look for to achieve is:

Get the cryptoword for the particular database record.
Use that cryptoword to decrypt the rest of the record.
Repeat the process for all the table records/multiple records satisfying WHERE condition, which I can add later

My query works, if I use the query for one record only. However, I need to get multiple records within from the table. Every record has its own cryptoword, which is different per each row. My task is therefore to get the cryptoword for particular record, and to use that one to decrypt the rest of the record. I need to repeat this process for all the table records.

Because of performance reasons, it all needs to be formatted within one query.

Thank you in advance.

You say for the particular database record yet you aren't filtering your results at all. You need a WHERE clause on both of your subqueries which will guarantee that only 1 record is returned. — Yuck
or LIMIT 1 at the end of each query to tell mysql to return only 1 row — CodeBird
I see what you mean. The challenge is, that I would need to apply my subquery for the whole table. — Bunkai.Satori
@MatBailie, great comment. This was my goal initially. However, while decrypting name in your query, cryptoword1 was not recognize. At least MySql has refused such query. Hmm, maybe I had done a mistake anywhere and have overlooked it? — Bunkai.Satori

MatBailie MatBailie · Accepted Answer · 2014-03-08T23:54:14

Work out the value of cryptoword1 in a sub-query, then you can reuse the result to work out the value of name in the outer query.

SELECT
  cryptoword1,
  AES_DECRYPT(name, SHA2(cryptoword1, 512))   AS name
FROM
(
  SELECT
    AES_DECRYPT(cryptoword, SHA2('DatabaseEncryption1', 512)) AS cryptoword1,
    name
  FROM
    file_tree
)
  AS sub_query

MySQL Error Message: Subquery Returns More than 1 Row

2 Answers