EXCEPT operator problem

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  October 24, 2018 at 8:29 am

  #380330

  I'm trying to update a set of rows and exclude several, but my EXCEPT operator is erroring:

  UPDATE a SET
a.Address1 = b.Address1, a.Address2 = b.Address2, a.city = b.city, a.stateAbbrev = b.stateAbbrev, a.phoneNumber = b.phoneNumber,
a.email = b.email, a.workPhone = b.workPhone, a.postalCode = b.postalCode, a.faxNumber = b.fax
FROM GCDF_DB..PeopleContactInfo a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
EXCEPT
SELECT b.peopleid
FROM BCC_DB..PeopleNotes b
WHERE b.peopleid in (16189,16535, 17521, 16434, 8859)
SELECT @@ROWCOUNT

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 24, 2018 at 8:33 am

  #2010934

  The error message is helpful in cases like this.  In its absence, I'll guess that it's because the two result sets don't have the same number of columns.

  John

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2018 at 8:34 am

  #2010935

  briancampbellmcad - Wednesday, October 24, 2018 8:29 AM

  I'm trying to update a set of rows and exclude several, but my EXCEPT operator is erroring:

  UPDATE a SET
a.Address1 = b.Address1, a.Address2 = b.Address2, a.city = b.city, a.stateAbbrev = b.stateAbbrev, a.phoneNumber = b.phoneNumber,
a.email = b.email, a.workPhone = b.workPhone, a.postalCode = b.postalCode, a.faxNumber = b.fax
FROM GCDF_DB..PeopleContactInfo a JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
EXCEPT
SELECT b.peopleid
FROM BCC_DB..PeopleNotes b
WHERE b.peopleid in (16189,16535, 17521, 16434, 8859)
SELECT @@ROWCOUNT

  Except expects both queries to have the same number of columns and datatypes.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2018 at 8:38 am

  #2010937

  Try this:
  
update [a]
set [a].[Address1] = .[Address1]
, [a].[Address2] = .[Address2]
, [a].[city] = .[city]
, [a].[stateAbbrev] = .[stateAbbrev]
, [a].[phoneNumber] = .[phoneNumber]
, [a]. = .
, [a].[workPhone] = .[workPhone]
, [a].[postalCode] = .[postalCode]
, [a].[faxNumber] = .[fax]
from
[GCDF_DB].[dbo].[PeopleContactInfo] as [a]
join [BCC_DB].[dbo].[PeopleNotes] as
  on [a].[peopleid] = .[peopleid]
where
.[peopleid] in (16189,16535, 17521, 16434, 8859);


• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 24, 2018 at 8:41 am

  #2010940

  Lynn Pettis - Wednesday, October 24, 2018 8:38 AM

  Try this:
  
update [a]
set [a].[Address1] = .[Address1]
, [a].[Address2] = .[Address2]
, [a].[city] = .[city]
, [a].[stateAbbrev] = .[stateAbbrev]
, [a].[phoneNumber] = .[phoneNumber]
, [a]. = .
, [a].[workPhone] = .[workPhone]
, [a].[postalCode] = .[postalCode]
, [a].[faxNumber] = .[fax]
from
[GCDF_DB].[dbo].[PeopleContactInfo] as [a]
join [BCC_DB].[dbo].[PeopleNotes] as
  on [a].[peopleid] = .[peopleid]
where
.[peopleid] in (16189,16535, 17521, 16434, 8859);


  Oops.  Change the IN to NOT IN.

• DaveBriCam

  SSCarpal Tunnel

  Points: 4298

  More actions

  October 24, 2018 at 8:44 am

  #2010941

  Thanks! works

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  October 24, 2018 at 4:19 pm

  #2011036

  UPDATE a SET
   a.Address1 = b.Address1
 , a.Address2 = b.Address2
 , a.city = b.city
 , a.stateAbbrev = b.stateAbbrev
 , a.phoneNumber = b.phoneNumber
 , a.email = b.email
 , a.workPhone = b.workPhone
 , a.postalCode = b.postalCode
 , a.faxNumber = b.fax
FROM GCDF_DB..PeopleContactInfo a
JOIN BCC_DB..PeopleNotes b ON a.peopleid = b.peopleid
WHERE b.peopleid NOT IN (16189,16535, 17521, 16434, 8859);

• jcelko212 32090

  SSCrazy Eights

  Points: 9303

  More actions

  October 25, 2018 at 4:14 pm

  #2011268

  Sybase created this ambiguous syntax (yes, it has some huge cardinality problems; it tends to use the last row physically stored on the disk when there are duplicates). This is why we invented the merge statement. Heres what I think you meant translated from dialect into’s into SQL

  MERGE INTO people_Contact_Info AS A
USING (SELECT people_id,
     address1, address2,
     city_name, state_code,postal_code,
     email_address,
     something_phone_nbr,work_phone_nbr, fax_phone_nbr
   FROM People_Notes
   WHERE B.people_id NOT IN (16189,16535, 17521, 16434, 8859)
ON A.people_id = B.people_id
WHEN MATCHED
  THEN UPDATE
  SET A.address1 = B.address1,
    A.address2= B.address2,
    A.city_name= B.city_name,
    A.state_code= B.state_code,
   A.something_phone_nbr =B.something_phone_nbr,
    A.email_address= B.email_address,
    A.work_phone_nbr= B.work_phone_nbr,
    A.postal_code= B.postal_code,
    A.fax_phone_nbr= B.fax_phone_nbr;
  [/code] 

  Please post DDL and follow ANSI/ISO standards when asking for help.