?
Solved

SQL Update Query

Posted on 2011-03-04
8
Medium Priority
?
614 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

 
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 2000 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

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

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

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 …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…

719 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