Need suggestions on Update query - Can I join the same table to do the update?
-
I have this table, let's call it "PrintTable".
I have the field called, letterID and JobID.
The field name, pdf_job_id is common.
The field name, Event, has three values; Generated, Received and Exit.
What we need to do is to update the null value in the letterID field with the LetterID , when the letterID is NULL and PDF_JOB_ID is the same.
the End result of line # 2 and # 3 will be: 24206, 3000037629.
If some records do not have the LetterID, such as Record # 7 and # 8, then, skip it.
******************************
I tried something like:
Update PrintTable
Set LetterID = ( ???
From
Select PrintTable a LEFT JOIN PrintTable b on a.pdf_job_id = b.pdf_job_id
Where a.PrintTable.letterID IS NULL
-- something like this
Is it possible to use the update query against the same table? Can anyone help? Thanks.
-
You could try something like the code below:
-- set up test data
if object_id('tempdb..#printtab') is not null
drop table #printtab
create table #printtab (
Letter_idvarchar(10),
job_idvarchar(5),
txtvarchar(5)
)
insert into #printtab
select '123','aaa','flg' union all
select null,'aaa','flg' union all
select null,'aaa','flg' union all
select '456','bbb','flg' union all
select null,'bbb','flg' union all
select '789','ccc','flg' union all
select '789','ccc','flg' union all
select null,'ccc','flg'
-- Check the table contents
select *
from #printtab
-- The update statement
update p1
set Letter_id = p2.Letter_id
from #printtab p1 inner join #printtab p2 on
p1.job_id = p2.job_id
where p1.Letter_id is null and
p2.Letter_id is not null
-
Thank you for your reply.
However, I have over 500,000 records in this table and I don't think it will work using the "union all".
Thanks.
-
Just to add, in the code above, my Job_id field refers to the Op's PDF_JOB_ID field.
-
You only need the update statement.
The code before that is me setting up test data on my machine so I could test that it works.
You just have to replace the #printtab tablename with your table name, and the column names from your table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply