update query with multiple fields in join

• phingers

  Ten Centuries

  Points: 1041

  More actions

  July 15, 2008 at 5:18 am

  #209991

  Hi,

  Is it possible to write an SQL which updates the records in a table based on the selected records from a query where the join is on more than one field?

  e.g.

  update TableA set the value = 1 where the id1 & id2 of TableA =

  (select id1, id2 from TableA inner join TableB on TableA.id1 = TableB.id1 and TableA.id2 = TableB.id2 where TableB.id = 12345)

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  July 15, 2008 at 5:23 am

  #842957

  Something like...

  update TableA set value = 1 from TableA inner join TableB on TableA.id1 = TableB.id1 and TableA.id2 = TableB.id2 where TableB.id = 12345

  which looks a bit nicer if you use table aliases...

  update a set value = 1 from TableA a inner join TableB b on a.id1 = b.id1 and a.id2 = b.id2 where b.id = 12345

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• phingers

  Ten Centuries

  Points: 1041

  More actions

  July 15, 2008 at 6:11 am

  #843001

  That's great, thanks!