Looking for SQL Assistance...

[Mel Riffe]

Howdy Folks!

I was hoping someone can point me in the right direction.

I have a table of associated data. I added to this table 'sequence_number' that is supposed to order the data within its owner.

OLD:

id

owner_id

name

NEW:

id

owner_id

sequence_number

name

Is there a SQL UPDATE Statement that can be crafted such that the sequence numbers for an owner_id can be populated 1, 2, 3, etc?

Thanks,
Mel

[Jacques Fuentes]

What SQL DB are you using? What version? Do the numbers change only upon update? Are you trying to make an audit log which doesn't record the mutated tuples but just the fact that a change was made? What's your use-case?

There are some options once we figure out what you're trying achieve: triggers (in various ways), a FOR UPDATE (explicit lock) which updates the row + the sequence_number afterwards.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.
To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

[Mel Riffe]

Doh! I should have provided the DB information in the original email.

MySQL 5.6

And this is for a migration, an initial load. After the data is loaded, the application will manage the sequence numbers.

On Mon, Nov 7, 2016 at 11:00 AM, Jacques Fuentes <jpfue...@gmail.com> wrote:

What SQL DB are you using? What version? Do the numbers change only upon update? Are you trying to make an audit log which doesn't record the mutated tuples but just the fact that a change was made? What's your use-case?

There are some options once we figure out what you're trying achieve: triggers (in various ways), a FOR UPDATE (explicit lock) which updates the row + the sequence_number afterwards.

On Mon, Nov 7, 2016 at 10:40 AM Mel Riffe <mri...@gmail.com> wrote:

Howdy Folks!

I was hoping someone can point me in the right direction.

I have a table of associated data. I added to this table 'sequence_number' that is supposed to order the data within its owner.

OLD:

id

owner_id

name

NEW:

id

owner_id

sequence_number

name

Is there a SQL UPDATE Statement that can be crafted such that the sequence numbers for an owner_id can be populated 1, 2, 3, etc?

Thanks,
Mel

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--

You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

[Jacques Fuentes]

In that case I'd consider using a trigger for the initial load which you delete later. Possibly after insert/update. Do you need some assistance looking into those options?

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

[Mel Riffe]

Yes, I would appreciate some assistance in the SQL.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva+unsubscribe@googlegroups.com.

[Al Tenhundfeld]

You should be able to do a query like this to generate sequence numbers for whatever grouping you want. Then you can take that output, join to the table, update, etc. I attached a full test script.

SELECT    id,

          owner_id,

          `name`,

( 

            CASE owner_id 

            WHEN @curOwnerId 

            THEN @curRow := @curRow + 1 

            ELSE @curRow := 1 AND @curOwnerId := owner_id END

          ) AS rank

FROM      test_table p,

          (SELECT @curRow := 0, @curOwnerId := 0) r

ORDER BY  owner_id, `name` asc;

Cheers,

Al

[Mel Riffe]

I believe next time I'll just directly email Al. :-D

Thank you very much for the test script too. Worked like a champ (of course - as if there was any doubt).

--Mel

[Jacques Fuentes]

Hah! Nice work Al!

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.

To post to this group, send email to refac...@googlegroups.com.
Visit this group at https://groups.google.com/group/refactorrva.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "RefactorRVA" group.

To unsubscribe from this group and stop receiving emails from it, send an email to refactorrva...@googlegroups.com.