Modify table with script

Hi,
please imagine the fields in mytable:

id
p1
p2_1
p2_2
p2_3
p3_1
p3_2
p3_3
p3_3t
p4

each "p" represents a page.

If I want to a have script to delete page 2 I can do something like this:

pageOrder = 2
Set rsc = Server.CreateObject("ADODB.Recordset")
sqlc = "SHOW COLUMNS FROM mytable LIKE 'p%'"
set rsc = cn.execute(sqlc)
                  
      On Error Resume Next
      rsc.MoveFirst
      Do While Not rsc.EOF
            if InStr(rsc("field"),"q"&pageOrder)>0 then
                  sqla = "ALTER TABLE mytable DROP COLUMN "&rsc("field")
                  cn.Execute(sqla)
            end if
      rsc.MoveNext
      Loop
                  
rsc.close
set rsc = nothing

But, I want the script also to change the fields of pages 3,4 to become 2,3 - How can I do it?
arielbfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
i think you should reconsider the table layout....
instead of adding columns, but normalize it like this:
id
page-id
page-data

and store the values 1,2,3 etc into page-id

this avoids table structure changes
hope this helps
0
arielbfAuthor Commented:
I understand your approach. But  I  can't have like that - so I need it to be "my way"... Thanks anyway.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Wondering why...

Anyhow you would need to rename the columns, one by one...
https://dev.mysql.com/doc/refman/5.1/en/alter-table.html
Alter table xxxx change colname newcolname
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

arielbfAuthor Commented:
Yes - I know how to modify columns one by one writing sql commands
But i need to script it
0
Walter RitzelSenior Software EngineerCommented:
in the lines of the script you wrote, you can replace the if statement for something like this:
if page order < rset(field) then
    command.execute(""alter table ...")
end if
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry to reply late, but I "disagree" with the above suggestion. at least, I see a serious issue with it, as the order of the columns presented by "show columns" is not the good on after adding/removing/altering columns.

while you may want to ignore this information, any reader of this "solution" should be aware of those issues.
to solve that, you may want to go this way: using the information_schema views instead
https://dev.mysql.com/doc/refman/5.0/en/columns-table.html

still, the "ordering" issue is present, and page 12 would eventually not come after page 9 but before, as the column names would be sorted by a name (string) and not by the numerical value representation.

good luck with your project
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.