May 11, 2007 at 12:07 pm
This is a follow up to the other thread I started. I finally figured everything out but need some validation before I hit go.
I want to update fields in one table using data on another server. What I want to avoid is the same mistake I made last time and only update records that have identical nc_incident_materials_id
I ran it against one record by using the hex data in place of nc_incident_materials_id in the WHERE clauses. Like this.
UPDATE nc_incident_materials
SET nc_materials_id = (SELECT offline.nc_materials_id
FROM dev.test_ed.dbo.nc_incident_materials AS offline
WHERE offline.nc_incident_materials_id = 0x0000000000049FB8)
WHERE nc_incident_materials_id = 0x0000000000049FB8
And this is the actual query I think I need to run
UPDATE nc_incident_materials
SET nc_materials_id = (SELECT offline.nc_materials_id
FROM dev.test_ed.dbo.nc_incident_materials AS offline
WHERE offline.nc_incident_materials_id = nc_incident_materials_id)
WHERE nc_incident_materials_id = offline.nc_incident_materials_id
May 11, 2007 at 3:41 pm
Tough crowd in here...
I was thinking. Could I have trouble accessing the 'offline' alias outside the brackets? How about this?
SET nc_materials_id = (SELECT offline.nc_materials_id
FROM dev.test_ed.dbo.nc_incident_materials AS offline
WHERE offline.nc_incident_materials_id = nc_incident_materials_id)
WHERE nc_incident_materials_id = (SELECT offline.nc_incident_materials_id
FROM dev.test_ed.dbo.nc_incident_materials AS offline
WHERE offline.nc_incident_materials_id = nc_incident_materials_id)
May 14, 2007 at 12:31 pm
Actually, I would use a query similar to this:
UPDATE online
SET [columnA] = offline.[columnA]
, [columnB] = offline.[columnB]
FROM nc_incident_materials AS online
JOIN dev.test_ed.dbo.nc_incident_materials AS offline
ON online.nc_incident_materials_id = offline.nc_incident_materials_id
However, your query as written doesn't do anything effective. You are updating the same value you are joining on. The values already match in that case, so there is no reason to update the nc_incident_materials_id column. Now, you can update other columns between the matching online and offline rows. In my example, these are [columnA] and [columnB].
May 16, 2007 at 8:06 am
As written you should get an error message, because your closing WHERE has an ambiguous reference? The interior select appears to be using only one table and is idempotent. If that's NOT how it gets interpreted, then you have an ambiguous reference. Or is your 'unqualified' reference to nc_incident_materials_id a place holder for a hard coded value or a variable?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply