Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2012
    Posts
    8

    Unanswered: Updating orders from Customer table

    Hi everyone,

    New to SQL but are learning, I have a simple problem that seems to be hard to do with my skills. I want to update the orders that has invoice number empty when doing a change in the customer table Nm.

    My order table is named order and customer is named customer, they both have a column named CustId and Nm (name).

    This works fine and shows in my case column Nm in 4 rows (for orders);

    select
    Order.Nm
    from
    Order
    Join Customer
    on Order.CustId=Customer.CustId
    where
    Order.CustId=@variable and InvoNo=''



    But the update statement gets error;

    Update
    Order
    set
    Order.Nm=Customer.Nm
    from
    Order
    inner join Customer
    on
    Order.CustId=Customer.CustId
    where
    Ord.CustId=@Variable and InvoNo=''

    I use SQl2008 and would be very glad if anyone could point me in the right direction.
    Last edited by MRM-Racing; 09-26-12 at 11:01.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Not sure what error you are getting. 'order" is a keyword, so you may want to avoid using it.

  3. #3
    Join Date
    Sep 2012
    Posts
    8
    I go this message;

    Msg 512, Level 16, State 1, Procedure Kst_order, Line 24
    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.



    And table are named Ord in my database.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why call your table "order" anyway? Surely it contains more than one of those.

    A more appropriate name would be: "orders"
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by gvee View Post
    Why call your table "order" anyway? Surely it contains more than one of those.

    A more appropriate name would be: "orders"
    You are right, We use Visma spftware with Sql database.

    And the table containing orders are named "Ord"
    table containing cutomers are named "Actor".

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I don't see a subqery in your example. Is this really your code?
    Code:
    Update Order
    set Order.Nm=Customer.Nm
    from Order inner join 
    	Customer on Order.CustId=Customer.CustId
    where Ord.CustId=@Variable 
      and InvoNo=''

  7. #7
    Join Date
    Sep 2012
    Posts
    8
    First off, THX for the help so far!

    If I query;

    Select Ord.OrdNo, Ord.CustNo, Ord.Nm
    From Ord
    join Actor
    on Ord.CustNo=Actor.CustNo
    where Actor.CustNo ='102434' and InvoNo=''

    I got this result;

    OrdNo CustNo Nm
    36673 102434 Brf Alphyddans Ros
    36672 102434 Brf Alphyddans Ros
    36671 102434 Brf Alphyddans Ros
    16229 102434 Brf Alphyddans Ros

    I want to update column Nm on these orders from Customer table named Actor and columns has same names.

  8. #8
    Join Date
    Sep 2012
    Posts
    8
    I try this query;

    update Ord
    set
    Nm=(select Nm from Actor where CustNo='102434')
    where CustNo='102434' and InvoNo=''

    Got this error;

    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.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MRM-Racing View Post
    I try this query;

    update Ord
    set
    Nm=(select Nm from Actor where CustNo='102434')
    where CustNo='102434' and InvoNo=''

    Got this error;

    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.
    Probably because there is more than one nm for custno '102434'.

  10. #10
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by corncrowe View Post
    Probably because there is more than one nm for custno '102434'.
    Yes I want to update all orders, but how should i query it?

    I have the orders table and customers table, when a customer change his address I want to run the query and update his orders with the new address.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What happens when you run just this part:
    Code:
    select Nm 
    from Actor 
    where CustNo='102434'
    Is there any unique constraint or key on the CustNo field?

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by MRM-Racing View Post
    Yes I want to update all orders, but how should i query it?

    I have the orders table and customers table, when a customer change his address I want to run the query and update his orders with the new address.
    Use a sql inner join predictor.

  13. #13
    Join Date
    Sep 2012
    Posts
    8
    Quote Originally Posted by MCrowley View Post
    What happens when you run just this part:
    Code:
    select Nm 
    from Actor 
    where CustNo='102434'
    Is there any unique constraint or key on the CustNo field?
    If i run ;

    select Nm
    from Actor
    where CustNo='102434'

    I got this;

    Nm
    Brf. Alphyddans Ros

  14. #14
    Join Date
    Sep 2012
    Posts
    8

    Thumbs up

    Problem solved!

    It turned out to be a trigger on the Orders table that did this error.

    This code is now working, thanks for your inputs!


    declare
    @Nm varchar(60)


    select @Nm=Nm from Actor where CustNo = '103083';


    disable trigger kst_order on ord

    Update
    Ord
    set
    Ord.Nm=@Nm
    where
    Ord.CustNo ='103083' and InvoNo='';

    enable trigger kst_order on ord

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •