CODEWITHSUNDEEP
sqlsql-server-2008
Eddy Whitaker
Eddy Whitaker

Reputation: 141

updating one table to another sql

ok..i'm sure i'm having a brain fart moment..but for some reason i cant get this update to work. I have 2 tables, i'm pretty much trying to copy info from 8 columns to 8 columns in another table. .. this is what i have so far

update a
set a.denialcharge_cost = b.denial_cost
    , a.epd_cost = b.early_default_payment
    , a.expeditecharge_cost = b.expeditecharge
    , a.duediligence_cost = b.duediligence
    , a.deskreview_cost = b.deskreview
    , a.servicing_cost = b.servicingcharge
    , a.mers_cost = b.merscharge
    , a.qcplans_cost = b.qcplans
from orderheader a 
inner join clients b
on a.prnt_id = b.id

i get the error

Msg 512, Level 16, State 1, Procedure UpdateOrderHeader, Line 13
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

i have x number of clients in clients..and y number of orders in orderheader..every order can have 1 client..every client can have multiple orders...prnt_id in orderheader has the id in the clients table....any help would be appreciated... i'm trying to copy denial_cost, early_default_payment, expeditecharge, duediligence, deskreview, servicingcharge, merscharge, qcplans from clients to orderheader

Upvotes: 0

Views: 1446

Answers (3)

swasheck
swasheck

Reputation: 4693

Based on this answer to a previous question, and all of the other troubleshooting that we did, it appears as though you have a trigger getting in the way somewhere. Try disabling it and running the update.

Upvotes: 2

DadViegas
DadViegas

Reputation: 2291

from what i understand of the error, you are trying do fetch only one result, somewhere and the subquery returns more than a field.

like

select (select * from bb) as count from cc gives an error

because the subquery returns more than a field from the nested query

Upvotes: 0

Turbot
Turbot

Reputation: 5227

SOMETHING like that should work (with formatting)

update orderheader
set denialcharge_cost = b.denial_cost, 
    epd_cost = b.early_default_payment,
    expeditecharge_cost = b.expeditecharge,
    duediligence_cost = b.duediligence,
    deskreview_cost = b.deskreview, 
    servicing_cost = b.servicingcharge,
    mers_cost = b.merscharge, 
    qcplans_cost = b.qcplans
from clients b 
where orderheader.prnt_id = clients.id

Upvotes: 0

Related Questions