How to construct set-based update stmts
-
Hey folks,
I'm trying to construct a procedure which will duplicate a block of users in one organization to another organization without having to resort to using cursors.
CREATE TABLE [Test_Users]([User_id] [int] IDENTITY(1,1) NOT NULL,
[org_id] [int] NOT NULL,
[first] [varchar](10) NULL,
[last] [varchar](10) NULL,
[local_id] [varchar](20) NULL
) ON [PRIMARY]
SET IDENTITY_INSERT [Test_Users] ON
INSERT INTO Test_Users ( User_id, org_id, first, last, local_id )
SELECT 1, 10, 'John', 'Doe', 'JDOE' UNION ALL
SELECT 2, 10, 'Jim', 'Jones', 'JJONES' UNION ALL
SELECT 3, 10, 'Bob', 'Barker', 'BBARKER' UNION ALL
SELECT 4, 10, 'Richard', 'Nixon', 'RNIXON' UNION ALL
SELECT 5, 50, 'NewJim', 'Jones', 'JJONES' UNION ALL
SELECT 7, 50, 'NewBob', 'Barker', 'NEWBEE' UNION ALL
SELECT 6, 50, 'NewBob', 'Barker', 'BBARKER'
SET IDENTITY_INSERT [Test_Users] OFF
SELECT * FROM Test_Users
So here's the scenario: We upload a block of users into the table under Org_ID = 50. I need to then basically duplicate all of the users in Org_ID = 10. The only thing I can be certain of here, is that I can JOIN Orgs 10 and 50 on the Local_ID field.
If I select all records from Org_Id = 50, I have my basic list of changes that need to be applied to Org_ID = 10. I will need to remove any records from Org_id = 10 if they aren't also in Org_id = 50. Seems reasonably easy since I only need a User_ID value to effect a delete. I can get that by doing this:
SELECT *INTO #tmpDELETE
FROM (SELECT user_id
FROM Test_Users AS u
WHERE u.Org_ID = 10
AND u.User_ID NOT IN (SELECT User_ID
FROM Test_Users
WHERE Org_ID = 50 ))
I think I can probably figure out something similar for the INSERT operation because the will be records for Org_ID = 50 that aren't in Org_id = 10's set.
The problem I'm struglling with here, is how to perform the updates (where there is a match on Local_ID between the two orgs.
I want to avoid inserting these folks as new records each time. I want to ensure that I update all of the columns except for the User_ID and the Org_ID. (The Local_ID value shouldn't matter since it should be the same...) I think I could do it with cursors, but I keep reading that cursors are a thing to avoid if at all possible. So is this possible to do without a cursor?
I think the resulting table should look like this:
User_idorg_idfirstlastlocal_id210NewJimJonesJJONES
310NewBobBarkerBBARKER
550NewJimJonesJJONES
750NewBobBarkerNEWBEE
650NewBobBarkerBBARKER
810NewBobBarkerNEWBEE
-
I believe you're looking for this:
UPDATE tu1SET
first = tu2.first,
last = tu2.last,
local_ID = tu2.local_id
FROM
#Test_Users AS tu1
JOIN
(SELECT
*
FROM
#Test_Users
WHERE
org_id = 50
) AS tu2
ONtu1.local_id = tu2.local_id
where
tu1.org_id = 50
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Many thanks Craig!
Your example was pretty damn close...with just a couple of minor tweaks I got it to work!
Here's the slightly corrected edition with the right table names:
UPDATE tu1SET
first = tu2.first,
last = tu2.last,
local_ID = tu2.local_id
FROM Test_Users AS tu1
JOIN
(SELECT *
FROM Test_Users
WHERE org_id = 50
) AS tu2
ON tu1.local_id = tu2.local_id
where
tu1.org_id = 10
-
Larry Kruse (10/6/2010)
Many thanks Craig!Your example was pretty damn close...with just a couple of minor tweaks I got it to work!
....
tu1.org_id = 10[/code]
Yawhoops... I'm the man of a thousand typos today. And thank you for posting a proper test harness that made it easy to work with. 🙂
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Take a look at the new MERGE command in BOL, might make a few things easier for you.
Hope this helps,
Rich[p]
[/p] -
Thanks Richard.
I'm looking at the BOL entry for MERGE (T-SQL) and it seems to indicate that it's used to merge two tables. Would it be able to work in the example I set forth above? The stuff in my example all resides in a single table. I actually simplified my example quite a bit because my actual user information resides in a pair of tables which I join using a view (for queries) or manually join on if I'm doing any kind of update/delete operation.
It could be most handy however if it can work from the same table. I will have to study the BOL description to see if I can figure it out and apply it in my case, because I will have INSERT/DELETE/UPDATE operations to perform with every reload of the initial organization's data.
Many thanks...I hadn't seen the MERGE command before.
-
Merge command is handy because it's basically used as an 'upsert', an insert/update.
First, you'd do your deletes separate.
The MERGE would then insert and update simultaneously according to your key. If the key existed, it would update, if not, it would insert.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply