Update a value from a query

I need to use the following select statement to update one table.

The table is documents..which you will see referenced in the query.

The primary key is in the query ..which is ndocument.

so in lamens

Update documents
set expiredate  = updatevalue  ( this is from the query)
where ndocument = the list of ndocuments in the query


Here is the query




SELECT d.ndocument, d.udf1, d.created,d.taxyear,t1.Name,t1.Parent,t1.Policy, t1.value,d.expirdate
,cast(D.taxyear as int) + 8 AS NewTaxYear,
CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101) as updatevalue

FROM Documents d
inner join
(
SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
    LEFT(value, CHARINDEX('!', value)-1) ELSE
    value END as value, name, Parent,
    CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
           END as Policy
  FROM [Cadoc_Ext].[dbo].[lookupexport] l

)t1
on d.udf1 = t1.value
where Policy = 8 and year(d.expirdate) != '9999'

Open in new window

LVL 11
Robb HillSenior .Net DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
I think something like this should work, could have been shorter as well but i used yours as subquery:
update 
documents 
set expiredate = UPD_SUBQ.updatevalue
fROM Documents
INNER JOIN 
(
	SELECT d.ndocument, d.udf1, d.created,d.taxyear,t1.Name,t1.Parent,t1.Policy, t1.value,d.expirdate
	,cast(D.taxyear as int) + 8 AS NewTaxYear,
	CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101) as updatevalue

	FROM Documents d
	inner join
	(
	SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
		LEFT(value, CHARINDEX('!', value)-1) ELSE
		value END as value, name, Parent,
		CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
			   END as Policy
	  FROM [Cadoc_Ext].[dbo].[lookupexport] l

	)t1
	on d.udf1 = t1.value
	where Policy = 8 and year(d.expirdate) != '9999'
)
as UPD_SUBQ
ON  Documents.ndocument = UPD_SUBQ.ndocument

Open in new window

1
Robb HillSenior .Net DeveloperAuthor Commented:
definately would like to see how you would reformat the query....I enjoy the feedback
0
Robb HillSenior .Net DeveloperAuthor Commented:
What would be most effective way to run this so if it messed up I can roll back....this one update will hit more than 1million records
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

Ares KurkluSoftware EngineerCommented:
I would definitely run in development environment first and test it, as you wrap it around
BEGIN TRAN
--ROLLBACK
--COMMIT

It will probably lock the tables for a while as it is 1 million records so you may want to do after hours etc.
May worth taking a backup creating a temporary table and store original values as well.
0
Nitin SontakkeDeveloperCommented:
I further suggest that you create a physical temp table with just two columns ndocument and updatevalue and create primary clustered key index on that table with ndocument as being the key column.

Make sure data types match exactly.

Use that table in join of the update query instead of the existing sub-query. Believe that it would much faster considering all the calculation have already been done and stored ready for use.
0
Mark WillsTopic AdvisorCommented:
It should be possible to do
update d  set expirdate = CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101)

FROM Documents d
inner join
(
SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
    LEFT(value, CHARINDEX('!', value)-1) ELSE
    value END as value, name, Parent,
    CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
           END as Policy
  FROM [Cadoc_Ext].[dbo].[lookupexport] l

)t1
on d.udf1 = t1.value
where Policy = 8 and year(d.expirdate) != '9999'

Open in new window

Beut given the potential impact, there is a bit of checking first

Would definitely test thoroughly first to the point of testing the result set first
SELECT d.ndocument, d.udf1, d.expirdate
    ,CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101) as updatevalue

FROM Documents d
inner join
(
SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
    LEFT(value, CHARINDEX('!', value)-1) ELSE
    value END as value, name, Parent,
    CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
           END as Policy
  FROM [Cadoc_Ext].[dbo].[lookupexport] l

)t1
on d.udf1 = t1.value
where Policy = 8 and year(d.expirdate) != '9999'

Open in new window

And if you are going to test, then there is a safer / more robust method....

To the point where you could do
SELECT d.ndocument, d.udf1, d.expirdate
    ,CONVERT(VARCHAR,( convert(varchar, cast(d.taxyear as int) + 8)) +'-'+'12'+'-'+'31', 101) as updatevalue

INTO tmp_documents_updatevalue_backup          -- Table needs to be uniquely named and must not already exist

FROM Documents d
inner join
(
SELECT CASE WHEN CHARINDEX('!', l.value) > 0 THEN
    LEFT(value, CHARINDEX('!', value)-1) ELSE
    value END as value, name, Parent,
    CASE WHEN value LIKE '%!%' THEN RIGHT(value,CHARINDEX('!',REVERSE(value))-1) 
           END as Policy
  FROM [Cadoc_Ext].[dbo].[lookupexport] l

)t1
on d.udf1 = t1.value
where Policy = 8 and year(d.expirdate) != '9999'

Open in new window

and go through tmp_documents_updatevalue_backup to double check and make sure no dupe values for ndocument and no strange date values in updatevalue

You could hen do the update from the tmp_documents_update_backup
update d  set expirdate = t.updatevalue
FROM Documents d
inner join tmp_documents_updatevalue_backup t on t.ndocument = d.ndocument

Open in new window


That way you have the original value as well if you need to roll back.

So, use the tmp_documents_updatevalue_backup approach :)
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
Apologies, think I dropped an "e" from expiredate and spelt it as expirdate above - trust you can adjust...
0
Robb HillSenior .Net DeveloperAuthor Commented:
Thanks for all answeres...still reviewing to determine...I thought there was a rollback method....so it looks like this backup a apprach is the best way storing primary key and changed value  and previous value as my safety net.
0
Ares KurkluSoftware EngineerCommented:
if you start your update with begin tran, then yes there is a rollback but as i said it will lock the table during the update until it is commited the table won't be accessible within the transaction once it is all done you can also run select statements to have a quick look to see if everything is as expected.

You can possibly divide it into chunks e.g like do it by 200.000 at once by using some sort of where clause on ID.

have a look at the begin transaction:
https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/
0
Robb HillSenior .Net DeveloperAuthor Commented:
yes I was ,lookin at that...but I have to run at sql 100   ...runis some of the fun
0
Mark WillsTopic AdvisorCommented:
If you want to run a few updates rather than all in one hit, you can control how much you put into the tmp table

e.g. 4 runs = select top 25 percent .... order by .....   and could select INTO tmp_documents_updatevalue_backup_part1,

Or, can control how many rows you select from the tmp table

I think the versatility of the tmp / backup table has a lot to offer.
0
Robb HillSenior .Net DeveloperAuthor Commented:
THank you all for your help and your expert suggestions!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.