Intersect against queries with different #'s of arguments

• Barkingdog

  SSCoach

  Points: 18670

  More actions

  February 8, 2009 at 12:15 pm

  #126210

  Setting: sql 2005:

  ----

  CREATE TABLE [dbo].[M](

  [c1] [nchar](10) NULL,

  [c2] [nchar](10) NULL,

  [c3] [nchar](10) NULL

  )

  truncate table M

  insert into M values (1,2,3)

  insert into M values (6,7,8)

  insert into M values (1,2,2)

  insert into M values (3,4,7)

  ----

  CREATE TABLE [dbo].[d](

  [mykey] [int] NULL,

  [c1] [nchar](10) NOT NULL,

  [c2] [nchar](10) NULL,

  [c3] [nchar](10) NULL,

  [isspecial] [bit] NULL

  )

  truncate table D

  insert into D values(1,1,2,2,0)

  insert into D values(2,1,2,3,0)

  insert into D values(3,3,4,5,0)

  ---

  select * from M

  select * from D

  Here's what I'm trying to do:

  update D

  set isspecial = 1

  (

  select c1,c2,c3 from M

  intersect

  select c1,c2,c3 from D

  )

  In other words, I want to set isspecial = 1 for those records in D that match (based on c1, c2, c3) those in M. Because Intersect requires the same number of columns in each query I can't make it do

  what I want. Any ideas much appreciated.

  TIA,

  Barkingdog

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 8, 2009 at 1:37 pm

  #940190

  Why use intersect? If you're looking for matches, join the tables

  update D

  set isspecial = 1

  from D inner join M on D.c1 = M.c1 AND D.c2 = M.c2 AND D.c3 = M.c3

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass