Table Maintenance 1

[mte0910]

My table looks like this...

Field1|Field2|Field3|Field4
RCD00 |Null |data3 |Null
RCD01 |data2 |Null |Null
RCD01 |Null |Null |data4
RCD01 |Null |data3 |Null

I need to run some sort of routine that will then look at all rows and where Field1 matchs, it will combine the data fields and "recommit" the data so that they table looks now like this...

Field1|Field2|Field3|Field4
RCD00 |Null |data3 |Null
RCD01 |data2 |data3 |data4

Is this even possible?
I'm not simply talking about a query, I want the entire table re-written each time this process is run.

 

[bborissov]

SELECT Field1,
       MAX(Field2) AS Field2,
       MAX(Field3) AS Field3,
       MAX(Field4) AS Field4
FROM YourTable
GROUP BY Field1

If this is what you want then:

SELECT Field1,
       MAX(Field2) AS Field2,
       MAX(Field3) AS Field3,
       MAX(Field4) AS Field4
INTO #Test
FROM YourTable
GROUP BY Field1

TRUNCATE TABLE YourTable
--- if this command didn't work because of 
--- some constraints use:
--- DELETE FROM YourTable

INSERT INTO YourTable
SELECT * FROM #Test

DROP TABLE #Test

And before trying the code above make a really, REALLY good backup first!!!!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[mte0910]

Worked Great!