Solved

SQL Update Query

Posted on 2011-03-04
8
610 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server maintenance plan 8 54
Split string into 3 separate fields 5 22
Not listening to where 1 24
Database Mail Profiles 1 25
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

730 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