• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 15
  • Last Modified:

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

0
Robb Hill
Asked:
Robb Hill
  • 5
  • 3
  • 3
  • +1
6 Solutions
 
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now