February 2, 2012 at 10:43 am
Hi, I need to update the records in table1 when these records exist in table2.
I need to run the update statement below and loop repeating the action for every match found in both tables. In the sample data provided below, I have only one doc_id but in the real data, I will have hundreds of doc_ids and detail lines.
Using cursors it should be simple to do this operation but I would like to know if someone can suggest a more efficient way.
Thanks,
Update table1
set status = 5
where doc_id = (select doc_id from table2
where table1.DOC_CD = table2.DOC_CD
AND table1.DEPT_CD = table2.DEPT_CD
AND table1.DOC_ID = table2.DOC_ID
AND table1.VERS_NO = table2.VERS_NO
AND table1.LN_NO = table2.LN_NO
AND DOC_ID = '12089030')
CREATE TABLE [dbo].[Table1](
[DOC_CD] [varchar](8) NULL,
[DOC_ID] [varchar](20) NULL,
[DEPT_CD] [varchar](4) NULL,
[LN_NO] [numeric](10, 0) NULL,
[VERS_NO] [numeric](10, 0) NULL,
[Status] [numeric](10, 0) NULL
)
CREATE TABLE [dbo].[Table2](
[DOC_CD] [varchar](8) NULL,
[DOC_ID] [varchar](20) NULL,
[DEPT_CD] [varchar](4) NULL,
[LN_NO] [numeric](10, 0) NULL,
[VERS_NO] [numeric](10, 0) NULL
)
Select * from table1
DOC_CDDOC_IDDEPT_CD LN_NO VERS_NO Status
IN 12089030 HO 12 NULL
IN 12089030 HO 11 NULL
IN 12089030 HO 21 NULL
IN 12089030 HO22 NULL
IN 12089030 HO32 NULL
IN 12089030 HO31 NULL
IN 12089030 HO42 NULL
IN 12089030 HO 41 NULL
IN 12089030 HO 51 NULL
IN 12089030 HO 52 NULL
IN 12089030 HO62 NULL
IN 12089030 HO61 NULL
IN 12089030 HO71 NULL
IN 12089030 HO 72 NULL
IN 12089030 HO 82 NULL
IN 12089030 HO 81 NULL
IN 12089030 HO 92 NULL
IN 12089030 HO 91 NULL
IN 12089030 HO 101 NULL
IN 12089030 HO 102 NULL
IN 12089030 HO 112 NULL
IN 12089030 HO111 NULL
IN 12089030 HO 121 NULL
IN 12089030 HO 122 NULL
IN 12089030 HO 132 NULL
IN 12089030 HO 131 NULL
Select * from table2
DOC_CDDOC_ID DEPT_CD LN_NO VERS_NO
IN 12089030 HO 1 2
IN 12089030 HO 2 2
IN 12089030 HO 3 2
IN 12089030 HO 4 2
IN 12089030 HO 5 2
IN 12089030 HO 6 2
IN 12089030 HO 7 2
IN 12089030 HO 8 2
IN 12089030 HO 9 2
IN 12089030 HO 10 2
IN 12089030 HO 11 2
IN 12089030 HO 12 2
IN 12089030 HO 13 2
select * from table1
where doc_id = (select doc_id from table2
where table1.DOC_CD = table2.DOC_CD
AND table1.DEPT_CD = table2.DEPT_CD
AND table1.DOC_ID = table2.DOC_ID
AND table1.VERS_NO = table2.VERS_NO
AND table1.LN_NO = table2.LN_NO
AND DOC_ID = '12089030')
Update table1
set status = 5
where doc_id = (select doc_id from table2
where table1.DOC_CD = table2.DOC_CD
AND table1.DEPT_CD = table2.DEPT_CD
AND table1.DOC_ID = table2.DOC_ID
AND table1.VERS_NO = table2.VERS_NO
AND table1.LN_NO = table2.LN_NO
AND DOC_ID = '12089030')
February 2, 2012 at 11:32 am
I would suggest checking BOL (Books On Line) for the MERGE STATEMENT.
SQL Server 2008 Books Online (July 2009)
Inserting, Updating, and Deleting Data by Using MERGE
or use this link
February 2, 2012 at 11:49 am
Here I am adding the insert statements to add the data to the tables.
Thanks,
Insert into table2
VALUES ('IN', '12089030', 'HO', 1, 2),
('IN', '12089030', 'HO', 2, 2),
('IN', '12089030', 'HO', 3,2),
('IN', '12089030', 'HO', 4,2),
('IN', '12089030', 'HO', 5,2),
('IN', '12089030', 'HO', 6,2),
('IN', '12089030', 'HO', 7,2),
('IN', '12089030', 'HO', 8,2),
('IN', '12089030', 'HO', 9,2),
('IN', '12089030', 'HO', 10,2),
('IN', '12089030', 'HO', 11,2),
('IN', '12089030', 'HO', 12,2),
('IN', '12089030', 'HO', 13,2);
INSERT INTO table1
VALUES ('IN', '12089030', 'HO', 1,2,NULL),
('IN', '12089030', 'HO', 1,1,NULL),
('IN', '12089030', 'HO', 2,1,NULL),
('IN', '12089030', 'HO',2,2,NULL),
('IN', '12089030', 'HO',3,2,NULL),
('IN', '12089030', 'HO',3,1,NULL),
('IN', '12089030', 'HO',4,2,NULL),
('IN', '12089030', 'HO', 4,1,NULL),
('IN', '12089030', 'HO', 5,1,NULL),
('IN', '12089030', 'HO', 5,2,NULL),
('IN', '12089030', 'HO',6,2,NULL),
('IN', '12089030', 'HO',6,1,NULL),
('IN', '12089030', 'HO',7,1,NULL),
('IN', '12089030', 'HO', 7,2,NULL),
('IN', '12089030', 'HO', 8,2,NULL),
('IN', '12089030', 'HO', 8,1,NULL),
('IN', '12089030', 'HO', 9,2,NULL),
('IN', '12089030', 'HO', 9,1,NULL),
('IN', '12089030', 'HO', 10,1,NULL),
('IN', '12089030', 'HO', 10,2,NULL),
('IN', '12089030', 'HO', 11,2,NULL),
('IN', '12089030', 'HO',11,1,NULL),
('IN', '12089030', 'HO', 12,1,NULL),
('IN', '12089030', 'HO', 12,2,NULL),
('IN', '12089030', 'HO', 13,2,NULL),
('IN', '12089030', 'HO', 13,1,NULL);
February 2, 2012 at 12:04 pm
Can you explain more clearly what you are trying to do? I don't see what a cursor has to do with this at all. Are you trying to update table1 and set the status to 5 when there is a corresponding doc_id in table2?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 2, 2012 at 12:06 pm
Out of curiousity, what's the problem with doing it this way:
Update t1
set status = 5
FROm
table1 AS t1
JOIn
table2 AS t2
ON
t1.DOC_CD = t2.DOC_CD
AND t1.DEPT_CD = t2.DEPT_CD
AND t1.DOC_ID = t2.DOC_ID
AND t1.VERS_NO = t2.VERS_NO
AND t1.LN_NO = t2.LN_NO
Never 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
February 2, 2012 at 12:24 pm
Kraig,your sample code works.
Thank you.
February 2, 2012 at 12:45 pm
josetur12 (2/2/2012)
Kraig,your sample code works.Thank you.
My pleasure. Good luck. 🙂
Never 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