Can this be done in a query or I must use a cursor? 1

[DeveloperJr]

Hi,

The problem is that I have a table contains historical data I have to import in the system. Before doing so, I am cleaning the data up as much as I can to match our standards.

One problem is, I have some related records (let’s say the order lines of an order) of which only one records populate some fields, the rest of the records have null for the same fields.
ID OrderID Field1 Field2 MoreFields
1 100 Value1 Value2
2 100 NULL NULL
3 200 ValueX ValueY
4 200 NULL NULL
I want to update the records that share the same parent (OrderID) with the information that exist in one of them. I know how to do this in programming languages like C#.

 

[bborissov]

You mean to update NULL values with the vaules of the other record which are not null, no matter what record it is?
What you want from this recordset:

ID  OrderID     Field1        Field2    MoreFields    
-------------------------------------------------------
1    100        Value1        Value2        
2    100        Value3        Value4        
3    100        NULL          NULL

4    200        ValueX        ValueY
5    200        ValueZ        ValueW
6    200        NULL           NULL

Did you care from what record Field1 and Field2 would be updated?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DeveloperJr]

Thanks for you reply. Normally, only one record will carry the information and the rest will either contains nulls for all/some fields; but the most consistent pattern I see so far is that only one record in each group has this info. In case multi records (in the same group) are populated, they will be populated with the same data not different one.

In other words, Value3 must be equal to Value1 and V4 = V2
ValueX = ValueZ and ValueW = ValueY.

Again, thanks for giving my question some of your time.

 

[bborissov]

If you have this data you could use:

[COLOR=blue]UPDATE[/color] YourTable
       [COLOR=blue]SET[/color] Field1 = [COLOR=#FF00FF]COALESCE[/color](YourTable.Field1, Tbl1.Field1),
       [COLOR=blue]SET[/color] Field2 = [COLOR=#FF00FF]COALESCE[/color](YourTable.Field2, Tbl1.Field2)
[COLOR=blue]FROM[/color] YourTable
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]SELECT[/color] OrderId,
                   [COLOR=#FF00FF]MAX[/color](Field1) [COLOR=blue]AS[/color] Field1,
                   [COLOR=#FF00FF]MAX[/color](Field2) [COLOR=blue]AS[/color] Field2
            [COLOR=blue]FROM[/color] YourTable
            [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] OrderId) Tbl1
[COLOR=blue]ON[/color] YourTable.OrderId = Tbl1.OrderId
[COLOR=blue]WHERE[/color] YourTable.Field1 [COLOR=blue]IS[/color] NULL OR
      YourTable.Field2 [COLOR=blue]IS[/color] NULL

(not tested at all)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[DeveloperJr]

Magnificent query. Works like magic, thank you so much.

The second SET is not acceptable by SQL-Server and I had to remove it. I am mentioning this for any one will read the thread and try to use the code.

 

[philhege]

And of course by that you mean

UPDATE YourTable
SET Field1 = COALESCE(YourTable.Field1, Tbl1.Field1),
Field2 = COALESCE(YourTable.Field2, Tbl1.Field2)
FROM YourTable
INNER JOIN (SELECT OrderId,
MAX(Field1) AS Field1,
MAX(Field2) AS Field2
FROM YourTable
GROUP BY OrderId) Tbl1
ON YourTable.OrderId = Tbl1.OrderId
WHERE YourTable.Field1 IS NULL OR
YourTable.Field2 IS NULL

The UPDATE statement takes only one SET.

Then, of course, bborisov did say it was untested, and I wager he typed it as he was running out the door ;^)

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
Not NULL-terminated yet.