Trying to update a table in several rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trying to update a table in several rows

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-04-29 : 05:59:42

How can I update a table like this?:

CertMana_Offers table

Cert_manaOffers_id bigint
Offer_num bigint (FK Offer_id)
Certificate_Mana smallint (FK Certificate_id)

With these returned parameters?: (which updates the Certificate_Mana column up to 7 possible rows)


@Certificate_Management0 smallint = nulll, @Certificate_Management1 smallint = null, 
@Certificate_Management2 smallint = null, @Certificate_Management3 smallint = null, 
@Certificate_Management4 smallint = null, @Certificate_Management5 smallint = null,
@Certificate_Management6 smallint = null

To make the first insert, I do it thus:


...

SET NOCOUNT OFF        

SET @Offer_id = SCOPE_IDENTITY()


SET NOCOUNT ON

INSERT INTO CertMana_Offers (Offer_num, Certificate_Mana)
SELECT @Offer_id, Certificate_Mana
FROM 
(    SELECT @Certificate_Management0 As Certificate_Mana UNION ALL
    SELECT @Certificate_Management1 UNION ALL
    SELECT @Certificate_Management2 UNION ALL
    SELECT @Certificate_Management3 UNION ALL
    SELECT @Certificate_Management4 UNION ALL
    SELECT @Certificate_Management5 UNION ALL
    SELECT @Certificate_Management6 
) AllCerts 
 WHERE Certificate_Mana IS NOT NULL

How can I make the ‘Update’ version for a specified ‘@Offer_id’?

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-04-29 : 06:58:57

You want to update the Certificate_Mana for the 7 rows for that @Offer_id?
You will need the Cert_manaOffers_id for each row.

You could do
update CertMana_Offers set Certificate_Mana = @Certificate_Management0
where Offer_num = @Offer_id
and Cert_manaOffers_id = (select top 1 * from (select top 1 Cert_manaOffers_id from CertMana_Offers where Offer_num = @Offer_id order by Cert_manaOffers_id) a order by Cert_manaOffers_id desc)
update CertMana_Offers set Certificate_Mana = @Certificate_Management1
where Offer_num = @Offer_id
and Cert_manaOffers_id = (select top 1 * from (select top 2 Cert_manaOffers_id from CertMana_Offers where Offer_num = @Offer_id order by Cert_manaOffers_id) a order by Cert_manaOffers_id desc)
...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cesark
Posting Yak Master

215 Posts

Posted - 2005-04-29 : 10:50:38

I think this is not what I want. I need to update rows that its Offer_num field it's equal to the given @Offer_id.

I suppose that I need to identify how many rows are filled with the given @Offer_id, then update the existing rows with ‘new’ @Certificate_Management0...6, and then see if I need to delete or insert more rows. For example if the there are three rows filled thus:

Cert_manaOffers_id = 267 / 268 / 269
Offer_num = 258 / 258 / 258
Certificate_Mana = 4 / 2 / 6

And I would receive:

@Certificate_Management0 = 1, @Certificate_Management1 = Null, @Certificate_Management2 = Null, @Certificate_Management3 = 3, @Certificate_Management4 = Null, @Certificate_Management5 = 2, @Certificate_Management6 = 5

The table would be: (update 3 rows and insert 1 new row)

Cert_manaOffers_id = 267 / 268 / 269 / 270
Offer_num = 258 / 258 / 258 / 258
Certificate_Mana = 1 / 3 / 2 / 5

And if I would receive:

@Certificate_Management0 = Null, @Certificate_Management1 = 4, @Certificate_Management2 = Null, @Certificate_Management3 = Null, @Certificate_Management4 = Null, @Certificate_Management5 = 2, @Certificate_Management6 = Null

The table would be: (update 2 rows and delete 2 rows)

Cert_manaOffers_id = 267 / 268
Offer_num = 258 / 258
Certificate_Mana = 4 / 2

And delete the rows 269 and 270 because there are only two certificates updated, and because Certificate_Mana column doesn’ t accept null values.

The logic would be something like this? Or Is there something wrong in my design that prevents of doing such operations (update a table like this)?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources