Solved

SQL Update Query

Posted on 2011-03-04
8
607 Views
Last Modified: 2012-05-11
Hi There

I have a table that looks like this

field1    feild2      feild3        field4
1              73       NED01       NED01
2              0         NED02        
3              0         NEDO3
4              73       NEDO4       NED04
5              0         NED06        
6              0         NED07    

I need to update field4 to be equal to field 3 for the record where field2 = 73
I need some sort of loop as this statement will be run against many records the outcome of the update should look like this.


field1    feild2      feild3        field4
1              73       NED01       NED01
2              0         NED02       NED01
3              0         NEDO3      NED01
4              73       NEDO4       NED04
5              0         NED06        NED04
6              0         NED07       NED04

Their might be more that three 3 records that need to be linked to the item above where field2 = 73

Thanks
Stanton
 
0
Comment
Question by:Stanton_Roux
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
Comment Utility
Unless I have misunderstood it is very simple...

Update MyTable Set Field4 = Field3 where Field2 = 72
0
 

Author Comment

by:Stanton_Roux
Comment Utility
Yeah its a little more complicated than that.

This is how the update must work where there is only one set of records

field1    feild2      feild3        field4
1              73       NED01       NED01
2              0         NED02       NED01
3              0         NEDO3      NED01

If there was only one record where feild2=73 then the statement will look like this

Update myTable set field4 = (Select field3 from myTable where field2=73)

But what I need to do is loop through the table and update all the records until the next record where field2=73

This is the Psuedo code for it


Loop through

Update myTable set field4 = (Select field3 from myTable where field2=73)
for all records under it until you find the next record where field2=73

repeat

I know it sounds complicated but it is quite difficult to explain






0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
Comment Utility
In that case, try this.    The inner query (newvalues) first identifies the parent under the assumption that field1 establishes the ordering.

Update MyTable
Set field4 = newfield4
From
MyTable inner join
(
      Select a.*, b.field4 as newfield4
       from
      (
      Select  C.*
       , parent = (Select max (field1) from MyTable P where field1<=C.field1 and P.field2 = 73)
      From MyTable C
      ) a
      inner join MyTable b
      on a.parent = b.field1
) newvalues
on MyTable.field1 = newvalues.field1
where MyTable.field2 <> 72
0
 
LVL 7

Expert Comment

by:printnix63
Comment Utility
What you describe raises some questions for me ...
a) you want to update field4 with the content of field3 where field2=73
but
b) you want to loop through this until you reach the next field2 that contains 73

This creates conflict, you are not updating anything else but those records that contain 73 in field2,
so why the looping, if the next field should abort?
ok ... from your example above, can we assume, that field1 contains just a "count", i.e. it can be assumed that
  you create a list of values (select field1,field2,field3,field4) where field2 = 73)
  and
  for each field1 that is between (value1_of_List(field1),value2_of_List(field1) )
  you update with field3(value(field1) < value2_of_list(field1) )

right?
 
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Expert Comment

by:printnix63
Comment Utility
Hi Paul_Harris_Fusion ... congrats ...
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
Comment Utility
UPDATE T1
SET field4=T2.field3
FROM tablename T1 CROSS APPLY
	(SELECT TOP 1 field3
	   FROM tablename
	   WHERE T1.field1 > field1
	         AND field2=73
	   ORDER BY field1 DESC
	) T2
WHERE T1.field2<>73

Open in new window


Test Script:
DECLARE @tablename table (field1  int,  field2  int,     field3 varchar(10),       field4 varchar(10))
INSERT @tablename 
SELECT 1, 73, 'NED01', 'NED01'
UNION ALL SELECT 2, 0, 'NED02', NULL
UNION ALL SELECT 3, 0, 'NEDO3 ', NULL
UNION ALL SELECT 4, 73, 'NEDO4', 'NED04'
UNION ALL SELECT 5, 0, 'NED06', NULL
UNION ALL SELECT 6,  0, 'NED07', NULL

UPDATE T1
SET field4=T2.field3
FROM @tablename T1 CROSS APPLY
	(SELECT TOP 1 field3
	   FROM @tablename
	   WHERE T1.field1 > field1
	         AND field2=73
	   ORDER BY field1 DESC
	) T2
WHERE T1.field2<>73

SELECT * FROM @tablename 
/*
field1  field2  field3  field4
1       73      NED01   NED01
2       0       NED02   NED01
3       0       NEDO3   NED01
4       73      NEDO4   NED04
5       0       NED06   NEDO4
6       0       NED07   NEDO4
*/

Open in new window

0
 

Author Closing Comment

by:Stanton_Roux
Comment Utility
Most Elegant solution thanks
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
Comment Utility
Here is an even simpler one.   It is derived from ThomasIan's solution.   I was wondering if I could refactor it without the CROSS APPLY and here it is...

UPDATE MyTable
SET field4=
( Select TOP 1 field3 FROM MyTable T2
  where T2.field1<MyTable.field1    and T2.field2 = 73
   order by field1 DESC
)
Where MyTable.field2 <> 73
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now