Insert a value based on a users location

Question

In mySql, Is it possible to do an insert based on something? In my case, I'd like to do an insert depending on the users location. Those locations are like US, UK, AU, CA etc. So if the location is US i'd like to do 1-0001, 1-0002 etc, for uk 2-001, 2-002 etc.

Maybe by using case of something like that this could be possible?

My current insert goes like this:

insert into prodClassifieds (userId, userName, classStatus, classCountry, classId)
   select
     $userId, 
     '$userName', 
     1,
     '$userCountry',
     IFNULL((MAX(classId)+1) ,0)
   FROM prodClassifieds

EDIT: I can leave out the - in 1-0001. Also the MAX(col) is MAX(classId) not the one I originally posted. The next value is 1 + the current value that's there.

Yes, it is possible with a `CASE`. Which value are you trying to modify? Which column results in `1-0001`? — Michael Berkowski, Dec 29 '12 at 13:53
If you are trying to construct those for `classId`, that is going to be more difficult since the `MAX()+1` is subject to problems from race conditions... Let us know how these will be used, and we can help. — Michael Berkowski, Dec 29 '12 at 13:55
@Norman Is this for a one-time data load, or will it by constantly updated from a live site? — Michael Berkowski, Dec 29 '12 at 13:57
See also [auto-incrementing by group](http://stackoverflow.com/questions/677542/auto-increment-by-group) (since that is sort of what you are attempting) — Michael Berkowski, Dec 29 '12 at 14:00
What is the field you wanna insert this calculated value into ? — Atheer Mostafa, Dec 29 '12 at 14:01
It will be constant updated from a live site. I can forgo the - in there. — Norman, Dec 29 '12 at 14:01
@Norman This is going to be complicated then. Is the table MyISAM or InnoDB? If it is MyISAM, you can use the AI per group as I linked earlier but consider if you _really_ need this the way you are asking. Perhaps you could just use a regular auto_increment and when you _query_ it, concatenate on the country prefix rather than store it that way. — Michael Berkowski, Dec 29 '12 at 14:06

Mari · Accepted Answer · 2012-12-29T13:59:57.870

0

Yes this is possible,

insert into prodClassifieds 
(userId, userName, classStatus, classCountry, classId) 
select $userId, '$userName', 1,
'$userCountry', 
 case when userCountry='US' then 
MAX(sc_stack_id)+1 else 0 end
FROM prodClassifieds

U can add several conditions as per your need

edited Dec 29 '12 at 13:59

answered Dec 29 '12 at 13:54

Mari

8,739
8
30
33

It is the last column (the `IFNULL()`) which the OP wants to apply this pattern. – Michael Berkowski Dec 29 '12 at 13:59
@MichaelBerkowski but this will always gives the same result for all countries right?? – Mari Dec 29 '12 at 14:03
That's why this is complicated, per my comments above. You can't just add 1 to a MAX() aggregate (especially without a GROUP BY). – Michael Berkowski Dec 29 '12 at 14:04
yeah @MichaelBerkowski thanks for guiding. Will group by country will solve the purpose? – Mari Dec 29 '12 at 14:06
It will work some of the time, but if 2 queries insert at the same time, a race condition could cause the values to become out of sync or the same value for MAX()+1 to be inserted for both. – Michael Berkowski Dec 29 '12 at 14:07
My "answer" is that I don't think this is a very good idea to begin with, and might be better off reengineered not to depend on increments per group :) – Michael Berkowski Dec 29 '12 at 14:13
right right. Thanks man. Am deleting my answer since this will not solve the purpose. Happy new year.. Keep rocking :) – Mari Dec 29 '12 at 14:16

score 0 · Answer 2 · answered Dec 29 '12 at 13:58

Use CASE WHEN as follows:

insert into prodClassifieds (userId, userName, classStatus, classCountry, classId)
   select
     $userId, 
     '$userName', 
     CASE '$userCountry' WHEN 'US' THEN 10000 WHEN 'UK' THEN 20000 ELSE 90000 END CASE
     + 1,
     '$userCountry',
     IFNULL((MAX(sc_stack_id)+1) ,0)
   FROM prodClassifieds

Insert a value based on a users location

2 Answers2