March 26, 2014 at 12:14 am
I am running a very large database with many large tables. This database tracks who did what in reference to maintenance on a vehicle, and is entered, viewed and used by a separate program in a nice, neat UI. The issue I have is that at some point in the past a mechanic entered his name incorrectly into the program, the name was added to the names table of the database along with his other information. when this happens the database creates a column within the row that his name is in of the names table called the name guid. The Guid is the only thing that is used throughout the rest of the database. for example... in the program when he does work he puts his name in a certain field and signs it off. this then in turn places his guid, not his name, into the name_guid column of the row and table that references the work performed in the program. the program then looks up this guid back in the name table to show his name in the program. So the only place his name is actually present is in the name table. Because of this it is quick and easy to change his name in the program by just changing his name in the name table, and it will reflect the correction anywhere in the program.... However due to the way our program is run it will only reflect that change if the row in which his guid has a value of true in the update_flag column.
So after that rather long explanation of what is going on my overall goal is to change the UPDATE_FLAG column value from "false" to "true" anywhere that that specific name guid is present, in any row of any table of the entire database. The NAME_GUID and UPDATE_FLAG are separate columns within the same row. and the columns are not in the same order in the different tables throughout the database, for example the NAME_GUID could be the second column, and the UPDATE_FLAG the 6th column of Table_A, But then could be the 8th and 14th columns of Table_B. The row that the name guid is in may be in multiple rows of the same table, and any row that has it present would need that same rows UPDATE_FLAG column value changed from "false" to "true"
If anyone has an idea of a script that could perform this, I would really appreciate the help, I know this is drawn out and ridiculous please let me know if you need any additional details.
March 26, 2014 at 2:44 am
OK lets start with the basics. This is day 1 of learning to script in sql for me so please correct me if I'm making an assuption to do something you can't do.
So far I learned to select the PID's guid (name guid) mentioned above in a table where that guid could be in one or two places, this is what I have:
select *
from MEMMH
where PID = 'xxx' or PID_STAMP = 'xxx'
1st question; is there a way to select from the entire database, not just a specific table? This 'xxx' could be in any table.
March 26, 2014 at 5:32 am
robertsquednau (3/26/2014)
OK lets start with the basics. This is day 1 of learning to script in sql for me so please correct me if I'm making an assuption to do something you can't do.So far I learned to select the PID's guid (name guid) mentioned above in a table where that guid could be in one or two places, this is what I have:
select *
from MEMMH
where PID = 'xxx' or PID_STAMP = 'xxx'
1st question; is there a way to select from the entire database, not just a specific table? This 'xxx' could be in any table.
Not in any particularly standard or elegant way, no.
You can, however, do it with Dynamic SQL (A good article on dynamic sql can be found here http://www.sommarskog.se/dynamic_sql.html)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply