Solved

Update SQL table rows based on values from other rows.

Posted on 2008-10-22
11
412 Views
Last Modified: 2012-05-05
I have a table where some of the rows did not get inserted correctly. The table looks like this.

DateTime                         Number
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       NULL
01/01/2001 08:30:22       NULL
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       NULL
01/01/2001 10:29:21       NULL
01/01/2001 10:29:21       429
01/01/2001 10:29:21       NULL
01/01/2001 10:29:21       429
01/01/2001 10:29:21       429

You can see a lot of the rows have the same datetime. some of these rows have value in the number column and some have a null. I need all the rows with the same time to have the same number value. So I need un update statement the will grab a row with a value in the number column and update all the rows that have that same datetime with the value in the number column. At the end the table would look like this.

DateTime                         Number
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       NULL
01/01/2001 08:30:22       NULL
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       5825
01/01/2001 08:30:22       5825
01/01/2001 10:29:21       5825
01/01/2001 10:29:21       429
01/01/2001 10:29:21       429
01/01/2001 10:29:21       429
01/01/2001 10:29:21       429





0
Comment
Question by:lyptus
  • 3
  • 3
  • 3
  • +2
11 Comments
 
LVL 23

Expert Comment

by:Kamaraj Subramanian
ID: 22776870
why these values

01/01/2001 08:30:22       NULL
01/01/2001 08:30:22       NULL

having NULL instead of 5825
0
 
LVL 4

Expert Comment

by:jindalankush
ID: 22776887
update A
set data1 = b.data1
from tab1 A,tab2 B
where A.date = b.date
and b.data1 is not null
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22776893
with a as (select min(number) as number, datetime from myTable group by datetime)

update t
set number = a.number
from mytable t inner join a on t.datetime = a.datetime
0
 

Author Comment

by:lyptus
ID: 22777130
jindalankush - that didnt't work.

momi_sabag - can you please elaboate. where is the select suppose to go?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 22777158
this is the entire statement

with a as (select min(number) as number, datetime from myTable group by datetime)
update t
set number = a.number
from mytable t inner join a on t.datetime = a.datetime

the with part performs the select
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 22

Expert Comment

by:dportas
ID: 22777188
What key(s) exist in this table? From your sample data it appears to have no key at all, which is a problem you should fix before attempting the solution you are asking for. Please include information about keys when posting a question such as this otherwise we can only guess.

I have another question. If there is more than one (non-null) number for any given datetime value then which of those numbers should be used to populate the null rows?

A guess (taking the minimum number in each case):

UPDATE tbl
SET number =
(SELECT MIN(t.number)
 FROM tbl t
 WHERE tbl.dt = t.dt);

0
 
LVL 22

Expert Comment

by:dportas
ID: 22777194
Small correction:

UPDATE tbl
SET number =
(SELECT MIN(t.number)
 FROM tbl t
 WHERE tbl.dt = t.dt)
WHERE number IS NULL;
0
 

Author Comment

by:lyptus
ID: 22777284
The PK for each row is a GUID.

the rows with non null values will have the same number for the same datetime. So if the datetime column is 01/01/2001 08:30:22 all the non null values in the number column will be the same for the given datetime.
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22777296
did you try my solution?
0
 
LVL 22

Expert Comment

by:dportas
ID: 22777321
lyptus: In that case you have what's called a non-key dependency and from a design perspective it would be much better to drop the number column from this table and create a new table with the datetime column as the key.
0
 

Author Closing Comment

by:lyptus
ID: 31508755
Thanks!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

759 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

18 Experts available now in Live!

Get 1:1 Help Now