September 15, 2015 at 3:23 am
Hello Experts,
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cdpermit_nbrreference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1991100003
ABC1992100004
ABC1993100005
ABC2880100006
ABC2881100007
ABC2881100007
ABC2882100008
ABC2882100008
Please help me in building the query.
Thanks in advance.
Naveen
September 15, 2015 at 5:12 am
I hope you are looking for this 🙂
CREATE TABLE #TABLE_1
(
job_cdvarchar(20),
permit_nbr int,
reference_nbr int
)
CREATE TABLE #TABLE_2
(
job_cdvarchar(20),
permit_nbr int,
reference_nbr int
)
INSERT INTO #TABLE_1
SELECT 'ABC1', 990, 100002
UNION ALL
SELECT 'ABC1', 990, 100002
UNION ALL
SELECT 'ABC1', 991, 100003
UNION ALL
SELECT 'ABC1', 992, 100004
UNION ALL
SELECT 'ABC1', 993, 100005
UNION ALL
SELECT 'ABC2', 880, 100006
UNION ALL
SELECT 'ABC2', 881, 100007
UNION ALL
SELECT 'ABC2', 881, 100007
UNION ALL
SELECT 'ABC2', 882, 100008
UNION ALL
SELECT 'ABC2', 882, 100008
INSERT INTO #TABLE_2
SELECT 'ABC1', 990, 100002
UNION ALL
SELECT 'ABC1', 991, 100003
UNION ALL
SELECT 'ABC1', 992, 100004
UNION ALL
SELECT 'ABC1', 993, 100005
UNION ALL
SELECT 'ABC2', 880, 100006
UNION ALL
SELECT 'ABC2', 881, NULL
UNION ALL
SELECT 'ABC2', 882, NULL
SELECT * FROM #TABLE_1
SELECT * FROM #TABLE_2
SELECT *,
Row_number() OVER(Order by job_cd,permit_nbr) AS row_num
INTO #max_num
from(
SELECT DISTINCT T1.*
FROM #TABLE_1 AS T1
LEFT OUTER JOIN #TABLE_2 AS T2
ON T1.job_cd=T2.job_cd AND
T1.permit_nbr = T2.permit_nbr
WHERE T2.reference_nbr IS NULL
)a
---FINAL QUERY
UPDATE T2
SET T2.reference_nbr=(CASE WHEN T2.reference_nbr IS NOT NULL THEN T2.reference_nbr
ELSE (SELECT MAX(itab.reference_nbr) + mn.row_num FROM #TABLE_1 itab) END)
FROM #TABLE_1 AS T1
LEFT OUTER JOIN #TABLE_2 AS T2
ON T1.job_cd=T2.job_cd AND
T1.permit_nbr = T2.permit_nbr
LEFT OUTER JOIN #max_num AS mn
ON T1.job_cd=mn.job_cd AND
T1.permit_nbr = mn.permit_nbr
September 15, 2015 at 6:48 am
if we have same job_cd and permit_nbr, reference number should be same
Is this for existing allocated reference number?
take max(reference number) from the table +1 for all rows where reference_nbr column is null
All of them have the same max(reference number)+1 irrespective of job_cd/permit_nbr grouping?
What does the data look like before and after update?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply