April 2, 2025 at 8:14 pm
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.
April 2, 2025 at 9:15 pm
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;
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply