[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

Update a table with a changing row id

Hi Team,

Just a quick request.

I want to update a table field with an incrementing number for a bulk load process.

This is what I have so far

select @crmseqid :=ID FROM vtiger_crmentity_seq;   -- This retrieves the last key used
SET @num = @crmseqid +1 ;                                      --  Adds 1 to key  
update bulk_crm_contacts ;                                        --  Specify table to update
SET CRMID = @num;                                                    -- Updates CRMID field
@num:=@num+1;


The table is showing the same CRMID for all rows; I'm brain dead at the moment.  What do I need to do here?

All help is appreciated.

Cheers
Bernard
0
BernardGBailey
Asked:
BernardGBailey
2 Solutions
 
ienaxxxCommented:
start by removing the semicolon afret update table assertion. Then i think you need a loop through the table, otherwise the only update is writing all the rows with the same number...
0
 
BernardGBaileyAuthor Commented:
No difference there
0
 
VipulKadiaCommented:
Try following lines of code (Written in MS-SQL 2005)

select @crmseqid =ID FROM vtiger_crmentity_seq  
SET @num = @crmseqid +1

DECLARE @OldCRMID BIGINT
DECLARE CrCRMID CURSOR local scroll for
Select CRMID FROM bulk_crm_contacts

OPEN CrCRMID
FETCH NEXT FROM CrCRMID INTO @OldCRMID
WHILE @@FETCH_STATUS=0
BEGIN
      Update bulk_crm_contacts SET CRMID=@num WHERE CRMID=@OldCRMID
      SET @num = @num + 1

      FETCH NEXT FROM CrCRMID INTO @OldCRMID
END

DEALLOCATE CrCRMID
CLOSE CrCRMID
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Bhavesh ShahLead AnalysistCommented:
Hi,

This is select query which will give you identity....

check out.

- bhavesh
select * from
(
select @rownum:=@rownum+1 rowno, p.* from friends p, (SELECT @rownum:=0) r
order by id desc limit 10
)finl
where rowno=2

Open in new window

0
 
kamalranjanCommented:
Hi Bernard,

I think you should simply use the feature from Database for this.

1) If you are having a SQL Server, make the field as Identity and specify the increment value of 1.

2) If you are having a MySQL, make the field as BigInt and select AutoIncrement checkbox.

Do specify the default value in either case.

Regards
Kamal
0
 
BernardGBaileyAuthor Commented:
VipulKadia

This what I ran in MySQL

select @crmseqid :=ID FROM vtiger_crmentity_seq;  
SET @num := @crmseqid +1;

DECLARE @OldCRMID BIGINT
DECLARE CrCRMID CURSOR local scroll for
Select CRMID FROM bulk_crm_contacts

OPEN CrCRMID
FETCH NEXT FROM CrCRMID INTO @OldCRMID
WHILE @@FETCH_STATUS=0
BEGIN
      Update bulk_crm_contacts SET CRMID=@num WHERE CRMID=@OldCRMID
      SET @num := @num + 1

      FETCH NEXT FROM CrCRMID INTO @OldCRMID
END

DEALLOCATE CrCRMID
CLOSE CrCRMID

---------------------------------------------------------------

The result message was
---------------------------------------------------------------

[SQL] select @crmseqid :=ID FROM vtiger_crmentity_seq;
Affected rows: 0
Time: 0.000ms

[SQL]  
SET @num := @crmseqid +1;
Affected rows: 0
Time: 0.000ms

[SQL]

DECLARE @OldCRMID BIGINT
DECLARE CrCRMID CURSOR local scroll for
Select CRMID FROM bulk_crm_contacts

OPEN CrCRMID
FETCH NEXT FROM CrCRMID INTO @OldCRMID
WHILE @@FETCH_STATUS=0
BEGIN
      Update bulk_crm_contacts SET CRMID=@num WHERE CRMID=@OldCRMID
      SET @num := @num + 1

      FETCH NEXT FROM CrCRMID INTO @OldCRMID
END

DEALLOCATE CrCRMID
CLOSE CrCRMID

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE @OldCRMID BIGINT
DECLARE CrCRMID CURSOR local scroll for
Select CRMID ' at line 1


Can you please expand on this?
Cheers
Bernard
0
 
BernardGBaileyAuthor Commented:
KamalRanjan,

This is a temp table which is being used for a bulk insert; I need to be able to extract the starting CRMID from the last CRMID already existing in the destination table which is being loaded. The destination table does not have autoincrement on and while I could change that, it would break the application.

All help is appreciated thanks
Bernard
0
 
BernardGBaileyAuthor Commented:
No solution provided I went back to extracting the data to a tmp table then passed it through a converion routine
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now