lyptus
asked on
Update SQL table rows based on values from other rows.
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
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
update A
set data1 = b.data1
from tab1 A,tab2 B
where A.date = b.date
and b.data1 is not null
set data1 = b.data1
from tab1 A,tab2 B
where A.date = b.date
and b.data1 is not null
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
update t
set number = a.number
from mytable t inner join a on t.datetime = a.datetime
ASKER
jindalankush - that didnt't work.
momi_sabag - can you please elaboate. where is the select suppose to go?
momi_sabag - can you please elaboate. where is the select suppose to go?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);
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);
Small correction:
UPDATE tbl
SET number =
(SELECT MIN(t.number)
FROM tbl t
WHERE tbl.dt = t.dt)
WHERE number IS NULL;
UPDATE tbl
SET number =
(SELECT MIN(t.number)
FROM tbl t
WHERE tbl.dt = t.dt)
WHERE number IS NULL;
ASKER
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.
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.
did you try my solution?
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.
ASKER
Thanks!
01/01/2001 08:30:22 NULL
01/01/2001 08:30:22 NULL
having NULL instead of 5825