How to update using data from three tables?

• mjdemaris

  SSC-Addicted

  Points: 499

  More actions

  April 2, 2025 at 8:14 pm

  #4572122

  I am in the process of migrating from MySQL to SQL Server.

  I have tables like this:

  create table OldTable (
  id int primary key,
  tagpath varchar(255),
          retired bigint
  )
  
  create table DataTable (
  tagid int not null,
  tagValue float
  )
  
  create table NewTable(
  id int primary key,
  tagpath varchar(255),
          retired bigint
  )

  The DataTable.tagid currently corresponds to the OldTable.id.  The tagpath in the Old and New tables are equal, meaning I could use

  where OldTable.tagpath = NewTable.tagpath

  I'm thinking something like this:

  UPDATE DataTable
  SET tagid = 
      (SELECT id from NewTable)
  WHERE NewTable.tagpath = OldTable.tagath

  But I am missing something.  I've seen it done before, but can't put my finger on what it's called or how to do it.  I want to say a CTE or maybe a subquery with an aliased reference to the outer query...

  Thanks for your help.

• Phil Parkin

  SSC Guru

  Points: 247133

  More actions

  April 2, 2025 at 9:15 pm

  #4572141

  Something like this, perhaps?

  UPDATE dt
  SET dt.tagid = nt.id
  FROM dbo.DataTable dt
      JOIN dbo.OldTable ot
          ON ot.id = dt.tagid
      JOIN dbo.NewTable nt
          ON nt.tagpath = ot.tagpath;

  ---