Solved

SQL Update Query

Posted on 2011-03-04
8
608 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
ID: 35035164
Unless I have misunderstood it is very simple...

Update MyTable Set Field4 = Field3 where Field2 = 72
0
 

Author Comment

by:Stanton_Roux
ID: 35035225
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
ID: 35035432
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 7

Expert Comment

by:printnix63
ID: 35035450
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
 
LVL 7

Expert Comment

by:printnix63
ID: 35035462
Hi Paul_Harris_Fusion ... congrats ...
0
 
LVL 22

Accepted Solution

by:
Thomasian earned 500 total points
ID: 35035722
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
ID: 35035854
Most Elegant solution thanks
0
 
LVL 12

Expert Comment

by:Paul_Harris_Fusion
ID: 35036144
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with query 3 25
This query failed in sql 2014 5 31
Need some help to cast ntext to nvarchar SQL 2000 7 33
Separate 2 comma delimited columns into separate rows 2 31
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

773 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