• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 317
  • Last Modified:

transferring old records to new table, but calculating (incrementing) lead ordinals per lead id

hi.
i am not sure if this can be done through a single mysql query, but it was attempted as follows:
// update test transactions table
$sql10 = "INSERT INTO 
			dwtphovu_8347379386_test.8_transactions 
			(
				bigint_TransactionID,
				text_TransactionEvent,
				bigint_SupplierID,
				bigint_LeadID,
				bigint_TransactionAmount,
				bigint_TransactionBalance,
				timestamp_TransactionEvent,
				smallint_LeadOrdinal
			) 
		SELECT 
			PTR.bigint_TransactionID,
			PTR.text_TransactionEvent,
			PTR.bigint_SupplierID,
			PTR.bigint_LeadID,
			PTR.bigint_TransactionAmount,
			PTR.bigint_TransactionBalance,
			PTR.timestamp_TransactionEvent, 
			#insert integers counting from 0 here, per lead id.
		FROM 
			dwtphovu_8347379386_prod.8_transactions PTR;";
$result10 = mysql_query_errors($sql10, $conn , __FILE__ , __LINE__ , true );

Open in new window

if this can be done only via php/mysql combined - how would this be achieved?
0
intellisource
Asked:
intellisource
  • 2
1 Solution
 
intellisourceAuthor Commented:
i'm wondering now, wether it would help to select a count with the insert of each record from the old table - for example:
// update test transactions table
$sql10 = "INSERT INTO 
			dwtphovu_8347379386_test.8_transactions 
			(
				bigint_TransactionID,
				text_TransactionEvent,
				bigint_SupplierID,
				bigint_LeadID,
				bigint_TransactionAmount,
				bigint_TransactionBalance,
				timestamp_TransactionEvent,
				smallint_LeadOrdinal
			) 
		SELECT 
			PTR.bigint_TransactionID,
			PTR.text_TransactionEvent,
			PTR.bigint_SupplierID,
			PTR.bigint_LeadID,
			PTR.bigint_TransactionAmount,
			PTR.bigint_TransactionBalance,
			PTR.timestamp_TransactionEvent, 
			(
				SELECT 
					COUNT(TT.smallint_LeadOrdinal) AS smallint_LeadOrdinal
				FROM 
					dwtphovu_8347379386_test.8_transactions TT 
				WHERE 
					TT.bigint_LeadID = PTR.bigint_LeadID
			)
		FROM 
			dwtphovu_8347379386_prod.8_transactions PTR;";
$result10 = mysql_query_errors($sql10, $conn , __FILE__ , __LINE__ , true );

Open in new window

0
 
intellisourceAuthor Commented:
and it seems to work beautifully ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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