Sql – Inline query to update multiple rows

oracleoracle10gsql

Here is a brief description of the tables I'm working with in Oracle 10g:

Notes:
Table : jnldetail : Single row with data as shown.
There are multiple package id's attached to the same bill_ref_no for an account. Therefore, I'm trying to update "jnldetail " with the multiple package_id's.

Relation between index_bill_ref and bill_ref_no : 1 – 1
Relation between account_no and ( index_bill_ref and bill_ref_no ) : 1 – Many

**Table : jnldetail** :
account_no     bill_ref_no     amount

8594822        74282843        822

I'm adding another column package_id with the following command:

alter table jnldetail add package_id number(10)


**table: bill_invoice**:

account_no    bill_ref_no       index_bill_ref

8594822       74282843          763653495


**table: bill_invoice_detail**:

index_bill_ref      package_id    component_id

763653495           20000077      20000177

763653495           20000250      20000528

763653495           13000019      13000137

**Expected Result:**
**Table : jnldetail** :

account_no     bill_ref_no     amount       package_id

8594822        74282843        822          20000077

8594822        74282843        822          20000250

8594822        74282843        822          13000019

My Query is:

UPDATE jnldetail tp
SET tp.package_id = (
    select 
         t1.package_id 
    from bill_invoice_detail t1
       , bill_invoice t2
    where 
         t1.index_bill_ref = t2.index_bill_ref 
      and
         t2.account_no = tp.account_no
)

The error message is : ora 01427 : single row subquery returns more than one row

Any inputs will be helpful.

Thanks!

Best Answer

The problem is that you're trying to set tp.package_id to more than one number, because your subquery is returning more than one result, e.g. 20000077 and 13000019. You'll need to alter the subquery so that only one value is returned.

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH ('')
            ) [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH ('')
        ), 2, 1000) [Students]
FROM dbo.Students ST2

Mysql – concatenate multiple MySQL rows into one field

You can use GROUP_CONCAT:

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Ludwig stated in his comment, you can add the DISTINCT operator to avoid duplicates:

SELECT person_id,
   GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY:

SELECT person_id, 
       GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:

SET group_concat_max_len = 2048;

Of course, you can change 2048 according to your needs. To calculate and assign the value:

SET group_concat_max_len = CAST(
                     (SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
                           FROM peoples_hobbies
                           GROUP BY person_id) AS UNSIGNED);

Best Answer

Related Solutions

Sql – How to concatenate text from multiple rows into a single text string in SQL Server

Mysql – concatenate multiple MySQL rows into one field

Related Topic