April 9, 2009 at 9:17 am
Hi,
I have problem while updating the same table.
My table:
CREATE TABLEConsultants(
consultant_keyint,
fiscal_period_keyint,
sort_seqtinyint,
item_keyint,
item_typetinyint,
item_descvarchar(100),
item_idvarchar(32),
reservation_indchar(1),
hoursfloat
)
i have inserted these values:
insert into Consultants values
(2855,5238,1,3793,2,'DP 1','S1-2462','N',55)
insert into Consultants values
(2855,5238,1,3794,2,'DP 1','S1-2462','N',10)
SELECT * FROM TempConsultantsFullSchedule
Now I just want to update the same table with sum of hours column and delete the second row. That means the row should be like:
2855,5238,1,3793,2,'DP 1','S1-2462','N',65
(65 = 55+10) and delete duplicate row.
Can anybody help me please.
KSB.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
April 9, 2009 at 9:26 am
What's the definition for the "item_key" column, since that's different in the two, while the other columns, other than hours, are all the same. If that column doesn't matter, then you could do something based on:
SELECT
consultant_key,
fiscal_period_key,
sort_seq,
item_type,
item_desc,
item_id,
reservation_ind,
SUM(hours) AS total_hours
FROM
#Consultants
GROUP BY
consultant_key,
fiscal_period_key,
sort_seq,
item_type,
item_desc,
item_id,
reservation_ind ;
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 11:40 pm
Hi GSquared,
Yeah, your query helped me. And now i have to update the same to the same table and need to delete the second row.
Thanks.
KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha
April 10, 2009 at 6:34 am
Hi
I supposed that the "first" row is identified by the MIN(item_key)...
So first update the Consultants and set the SUM of hours to the first Consultant, then delete all other rows:
; WITH
first_consultant (cons_key, item_key, hours) AS
(
SELECT consultant_key, MIN(item_key), SUM(hours)
FROM Consultants
GROUP BY consultant_key
)
UPDATE c SET c.hours = fc.hours
FROM Consultants c
JOIN first_consultant fc ON c.consultant_key = fc.cons_key AND c.item_key = fc.item_key
; WITH
first_consultant (cons_key, item_key) AS
(
SELECT consultant_key, MIN(item_key)
FROM Consultants
GROUP BY consultant_key
)
DELETE c
FROM Consultants c
LEFT JOIN first_consultant fc ON c.consultant_key = fc.cons_key AND c.item_key = fc.item_key
WHERE fc.cons_key IS NULL
... sometimes a CROSS APPLY solution may be faster than an aggregation but I don't know yet when and when not...
Greets
Flo
April 10, 2009 at 9:49 am
GSquared (4/9/2009)
SELECT
consultant_key,
fiscal_period_key,
sort_seq,
item_type,
item_desc,
item_id,
reservation_ind,
SUM(hours) AS total_hours
FROM
Consultants
GROUP BY
consultant_key,
fiscal_period_key,
sort_seq,
item_type,
item_desc,
item_id,
reservation_ind ;
1) Insert the above query results into a table(Consultants_new).
2) Rename the old table Consultants to Consultants_old.
3) Rename the old table Consultants_new to Consultants.
Thats it :w00t:
April 10, 2009 at 9:56 am
Vijaya Kadiyala (4/10/2009)
1) Insert the above query results into a table(Consultants_new).2) Rename the old table Consultants to Consultants_old.
3) Rename the old table Consultants_new to Consultants.
Thats it :w00t:
Hi Vijaya Kadiyala
I'm not sure if manipulation of database objects should be the way to go. This may break dependencies, require recreation of indexes and constraints, rights. If any other connection tries to work with this table while you do that it will crash.
Greets
Flo
April 10, 2009 at 10:04 am
Florian Reischl (4/10/2009)
Vijaya Kadiyala (4/10/2009)
1) Insert the above query results into a table(Consultants_new).2) Rename the old table Consultants to Consultants_old.
3) Rename the old table Consultants_new to Consultants.
Thats it :w00t:
Hi Vijaya Kadiyala
I'm not sure if manipulation of database objects should be the way to go. This may break dependencies, require recreation of indexes and constraints, rights. If any other connection tries to work with this table while you do that it will crash.
Greets
Flo
Ho Flo,
I agree with you. It depends on few factors:
1) Volume of the data.
2) Time of update Operation.
3) Dependecy with any other objects.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply