SQL: How do I increment an alphanumeric field by + 1?

February 4, 2012 7:52 AM

I'm a bit of a SQL novice but learning at a decent clip. I'm using SQL (Aqua Data Studio, if that matters to you) to update a couple of fields in an Oracle database table. I'd like to do this across a range of one of the fields. One field is a numeric field (trans_id - a transaction id) that I need to increase by 1 -- I think have that part licked. The next field is alphanumeric (calendar_name - represents the name of a week) and also needs to be incremented by one. The doozy (for me) is the alphanumeric field. Who's the guru?

The numeric portion of each is the same (trans_id 201119 corresponds to calendar_name W201119). Examples:
trans_id (numeric field): 201119 updates to 201120, 201130 updates to 201131, 201148 updates to 201149, etc.
calendar_name (alphanumeric field): W201119 updates to W201120, W201130 updates to W201131, W201148 updates to W201149, etc.

Here's the UPDATE statement I wrote (it's obvious the calendar_name cannot be updated as written but you probably get the idea of what I'm after if you're reading this.):

UPDATE ekbf.pkb_actual_receipts
SET trans_id = (trans_id + 1), calendar_name = (calendar_name + 1)
WHERE calendar_name between 'W201119' and 'W201148';

posted by brokeaspoke to computers & internet (10 answers total)

So, will these fields ever be out of sync? By which I mean are they just two representations of the same data?

If the prefix changes, make it a separate field 'week_name'

Your where clause can be "WHERE week_name='W' and trans_id between 'W201119' and 'W201148';

Or if you must have a separate field, you could use a view.

If trans_id and calendar_name's numeric value do get out of sync, then you might need three fields.
posted by Mad_Carew at 8:40 AM on February 4

They will always be in sync and the prefix of the alphanumeric field will always be W. There is one table with an exception to that convention. I also need to be able to do this without adding another field as this table is created by our software vendor and for support reasons we can't really add another field unless it fits any number of metric types or criteria already supported or set by the vendor.
posted by brokeaspoke at 8:48 AM on February 4

Leaving aside the craziness of having a separate column that is nothing more than the transaction id with a W in front of it... what I would do in mysql is do 2 separate queries. The first one would be to update the transaction ids then the second one would update the calendar_name to "W"+trans_id

In mysql I would do eg. Update pkb_actual_receipts set calendar_name concat('W',trans_id) where calendar_name between 'W201119' and 'W201148'

Never used Oracle so I don't know for sure that this would work but I believe that translates into something like:
update ekbf.pkb_actual_receipts set calendar_name = ||'W'||trans_id where calendar_name between 'W201119' and 'W201148'
posted by missmagenta at 8:53 AM on February 4

I'm not very familiar with Oracle, but the way you usually do these things is to take the string appart, increment the numerical part and then glue everything back together. Something like the following should work:

calendar_name='W'||(SUBSTR(calendar_name,2,6)+1)

The way these fields are set up is a little suspect - what happens if the two are out of sync, and which one will you use in the WHERE clause? - but I guess the people designing your db had some good reason for it.

One thing to be carefull of with your updates is corner cases - what should W201152 update to?
posted by Dr Dracator at 8:56 AM on February 4

By design, the values in the two columns will never be out of sync unless someone (me?) botches it up manually with the wrong query. We have several tables using the same conventions except one table which in the case of calendar_name W201103 the trans_id might be 20110121 (an actual calendar date). So much for conventions, right?

This is a staging table where source data will be gathered and from which the data is copied to proprietary forecasting and planning software. When the copy is made the trans_id is not copied but the calendar_name is copied.
posted by brokeaspoke at 9:26 AM on February 4

Luckily corner cases are a non-issue as we have no reason to update W201152 since we can always update records with W201151 to W201152. My question is all in the name of adding data from a legacy fiscal year calendar to a system which has inbuilt support for our current fiscal year calendar. Luckily the calendars are only off by a week.
posted by brokeaspoke at 9:30 AM on February 4

UPDATE ekbf.pkb_actual_receipts
SET trans_id = (trans_id + 1), calendar_name = 'W' || (trans_id + 1)
WHERE calendar_name between 'W201119' and 'W201148';


That would do it, right? I'm not familiar with Oracle, only SQL Server. And I can't check that right now, unfortunately--you may have to CAST((trans_id + 1) AS CHAR(6)) or something before the concatenation, I'm not sure (I think that happens automatically though).

For week 52, you could use a CASE statement. When trans_id % 100 = 52, calendar_name = 'W' || (trans_id + 48).

As missmagenta pointed out, if trans_id and calendar_name are always similar like this, you don't need to go through the process of stripping out the integer from calendar_name--just use trans_id. (I think you can do it just fine in one query, though).
posted by kprincehouse at 9:38 AM on February 4 [1 favorite]

Er...
calendar_name = 'W' || (trans_id + 49).

If you're at week 52, then I guess that means the first week of the year is 1, not 0.
posted by kprincehouse at 9:41 AM on February 4

Your examples don't illustrate the incrementing of the alphanum field. That said, since you're using Oracle I would look into using composite keys (though your vendor situation may prevent this).
posted by rhizome at 11:01 AM on February 4

Also, before you start running queries on your production db, make a copy of the table and run them on that, just to make sure they do what you think they'll do. Or at least make a backup copy of the db so you have something to fall back on, just in case.
posted by exphysicist345 at 11:08 AM on February 4