Copy all values of a column from one table to another

[himanshupancholi]

Hi,
I need to Copy all values of a column from one table to another. Below are the details:
Source: STL_GRP table, VEND column
Destination PARTNER table, VEND column.

I am using the below query which is incorrect:
[code=oracle]

UPDATE PARTNER
SET VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
)
[/code]

This query is returning error: "ORA-01427: single-row subquery returns more than one row", which is true.

Can someone suggest an alternative to do this? Any help will be appreciated. Thanks.

[chaarmann]

What do you expect the database to return if it found two rows in the second table that matches the current row? And also these values are different??? (so they are not filtered out by DISTINCT?
Take the first one or the second one? Concatenate data of first and second one and then store?

I mean you must make your "where" clause in a way that exactly one record of source table matches exactly one record (or none) in destination table.
You can do that by making sure the matching keys PRT.PARTNER_CD = STL.PARTNER_CD are unique in both tables. Or filter out more inside your where clause, by matching with a second column.
Or matching with min(rownum) to get the first record.

Hi,
I need to Copy all values of a column from one table to another. Below are the details:
Source: STL_GRP table, VEND column
Destination PARTNER table, VEND column.

I am using the below query which is incorrect:

UPDATE PARTNER
SET VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
)

This query is returning error: "ORA-01427: single-row subquery returns more than one row", which is true.

Can someone suggest an alternative to do this? Any help will be appreciated. Thanks.

[amitpatel66]

Try using EXISTS and check if it works.

[code=oracle]

UPDATE PARTNER
SET VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
)
WHERE EXISTS (SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD)

[/code]

[chaarmann]

So what is the difference of the original and the solution below?
The SQL below does not solve the issue with 2 matching records.
The only difference I can see is that if a record does not exist in second table, no copy into first table is done instead of assigning null value there.

Try using EXISTS and check if it works.

[code=oracle]

UPDATE PARTNER
SET VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
)
WHERE EXISTS (SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD)

[/code]

[amitpatel66]

So what is the difference of the original and the solution below?
The SQL below does not solve the issue with 2 matching records.
The only difference I can see is that if a record does not exist in second table, no copy into first table is done instead of assigning null value there.

Mistake!! I meant with few more conditions or with Aggregate function to select single value from more than one records returned by the query!!

[himanshupancholi]

I will re-phrase the problem. If there are multiple entries of VEND in STL_GRP, then first entry should be picked up.
Now, certainly below query will not help me, as internal query returns all the VEND values not just first one. Can you suggest something?

[code=oracle]

UPDATE PARTNER
SET VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL, PARTNER PRT
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
)

[/code]

[amitpatel66]

Try This:

[code=oracle]

UPDATE PARTNER PRT
SET PRT.VEND=
(
SELECT DISTINCT STL.VEND
FROM STL_GRP STL
WHERE PRT.PARTNER_CD = STL.PARTNER_CD
AND STL.ROWID = (SELECT MAX(ROWID) FROM STL_GRP ST WHERE PRT.PARTNER_CD = ST.PARTNER_CD))

[/code]

[John Boy]

try limit 1 if you only want the first returned....

UPDATE PARTNER 
SET VEND= 
( 
    SELECT DISTINCT STL.VEND 
    FROM STL_GRP STL, PARTNER PRT 
    WHERE PRT.PARTNER_CD = STL.PARTNER_CD limit 1
)