Post reply

Query ok on server - not working on another

  • July 25, 2006 at 4:25 pm

    #113109

    update nimages  set nimages.evox_id = (select evox_id from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb)

     

    Is the statement I'm using.

    I use on our dev environment (which I just found out needs the 3a service pack), but

    when I migrated it to our staging environment (which has the 3aSP) I get the following error -

     

    Server: Msg 512, Level 16, State 1, Line 1

    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 understand that I need to update the system, but was still wondering what do I need to change in my query to get it work? What option changed?

    Thanks in advance

    Susan

     

  • July 25, 2006 at 4:41 pm

    #651505

    What are the results of this part of your query on each server?

    select evox_id from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb

  • July 25, 2006 at 4:46 pm

    #651508

    Sorry about that !!

     

    When I just run the subquery, I get this both times

    Server: Msg 107, Level 16, State 3, Line 1

    The column prefix 'nimages' does not match with a table name or alias name used in the query.

  • July 25, 2006 at 4:46 pm

    #651506

    Sorry about that !!

     

    When I just run the subquery, I get this both times

    Server: Msg 107, Level 16, State 3, Line 1

    The column prefix 'nimages' does not match with a table name or alias name used in the query.

  • July 25, 2006 at 4:51 pm

    #651509

    You would need to use IN instead of "=" in yout original query. I would check the result set first before doing the UPDATE.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • July 25, 2006 at 4:57 pm

    #651510

    Here's what I changed

     

    update nimages  set nimages.evox_id in (select evox_id from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb

     

    Now I get this error message

    Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'in'.

  • July 25, 2006 at 5:07 pm

    #651512

    you dont "SET" , "IN". You set a value WHERE the ID is IN a given a list of values.

    what exactly are you trying to do?

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • July 25, 2006 at 5:11 pm

    #651513

    based on what I understand you are trying to set the evox_id of nimages to the value from vehicle_tmp? If so perhaps something like this?

    UPDATE ni

    SET ni.evox_id = v.evox_id

    FROM

    nimages ni

    JOIN vehicle_temp v

    ON v.vehicle_nmb = ni.vehicle_nmb

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • July 25, 2006 at 7:07 pm

    #651519

    Susan, there is nothing to do with Service Packs.

    Look at yor data first.

    In Staging table vehicle_tmp just contains 2 or more lines  with the same vehicle_nmb. And your query is too bad to handle this.

    Solution from Dinakar will work, but you can never guess which of lines with the same vehicle_nmb will supply evox_id to updated table.

     

    _____________
    Code for TallyGenerator

  • July 26, 2006 at 8:14 am

    #651634

    You could just apply the smallest or largest evox_id using Min() or Max(), like this:

    update nimages

    set nimages.evox_id = (select Min(evox_id) from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb)

  • July 26, 2006 at 9:31 am

    #651674

    Thanks for all your help.

    SUsan

     

  • July 27, 2006 at 12:19 am

    #651792

    Hi Susan,

    If you are getting a unique evox_id  value for nimages.vehicle_nmb from vehicle_tmp then you can use top 1 in order to get your desire output.

    update nimages  set nimages.evox_id = (select top 1 evox_id from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb)

    Thanks

    Babita

  • July 27, 2006 at 12:19 am

    #651791

    Hi Susan,

    If you are getting a unique evox_id  value for nimages.vehicle_nmb from vehicle_tmp then you can use top 1 in order to get your desire output.

    update nimages  set nimages.evox_id = (select top 1 evox_id from vehicle_tmp v where v.vehicle_nmb = nimages.vehicle_nmb)

    Thanks

    Babita

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply