Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bmfjon
Starting Member
14 Posts |
 Posted - 2007-11-16 : 20:24:29
|
| HiI'm new to SQL and have a problem with the following script:-INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)VALUES 2311, 19219, 'BRAN', '01/12/2007', 'Jon')The above script works OK on a one-off basis but....Basically, organisation_number_2 (i.e. 19219) always stays the same. However, I need to update organisation_number_1 several times (i.e. 2311 will then change to 2312, 2313, 2314 etc.).Rather than pasting the script several hundred times and changing the organisation_number_1 value each time, is there a quick way to encompass all the organisation_number_1 values in one go?I've tried e.g. VALUES (2311,2312), 19219, 'BRAN' etc. but this doesn't seem to work.Thanks for your help.Jon |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-16 : 20:42:30
|
[code]INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)SELECT organisation_number_1, 19219, 'BRAN', '01/12/2007', 'Jon'FROM ( select 2311 as organisation_number_1 union all select 2312 ) v[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
bmfjon
Starting Member
14 Posts |
Posted - 2007-11-17 : 05:24:06
|
| Hi!Thanks for the tip. So, should the figure in the last SELECT after UNION ALL be the highest number that I wish to change (i.e. so it will change all those numbers between 2311 and the last figure?).Sometimes the numbers skip quite a few, so I can do a run from 2311-2320, and then the next set of numbers will be 2567-2590 for example.How would I do this?Many thanks for your help.Jon |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-17 : 06:05:43
|
[quote]So, should the figure in the last SELECT after UNION ALL be the highest number that I wish to change (i.e. so it will change all those numbers between 2311 and the last figure?).]/quote]It is not. It is just a record. If you wanted a range like 2311 - 2320, use the F_TABLE_NUMBER_RANGE function
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bmfjon
Starting Member
14 Posts |
Posted - 2007-11-17 : 06:23:10
|
| Hi... thanks again!Just one more question....I've tried the following statement so that I can do several at once but it doesn't seem to work. What do I need to do?INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)SELECT organisation_number_1, 19219, 'BRAN', '01/12/2007', 'Jon'FROM ( select 2507 as organisation_number_1 union all select 523 select 524 select 562 select 563 select 622 select 653 select 659 ) v |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-17 : 07:08:01
|
[code]INSERT INTO organisation_links (organisation_number_1, organisation_number_2, relationship, amended_on, amended_by)SELECT organisation_number_1, 19219, 'BRAN', '01/12/2007', 'Jon'FROM ( select 2507 as organisation_number_1 union all select 523 union all select 524 union all select 562 union all select 563 union all select 622 union all select 653 union all select 659) v[/code]
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
bmfjon
Starting Member
14 Posts |
Posted - 2007-11-17 : 07:13:59
|
Oh yes.... pretty obvious really, I guess!!!Thanks for all your help.... you've really made my day easier!  Jon |
|
|
|
|
|
|
|
|
Updating several values at the same time