Rockin' the SQL
I had a great day at work today. In fact, this week has been pretty great at work.
There are just too many projects to do and too many minor crises that have to be managed. It's terrible. Because it's so hard to get priorities aligned properly, I've been a tad unfocused.
But that's ok. We all know that this one manager makes work. She's very smart and a nice friend to have. There is a specific thing she's needed done for a long time and it was a challenge just testing the damned thing to make sure it was being done right. I never got it to work and other work kept bumping down my list. (My BFL! Yes, daruba! I use "BFL" at work!)
Well, 6 months later, I'm looking at the problem again, and have decided a whole new tactic. I am going to materialize a section of a view and then use that table to store the values I need to update. In doing so, I have a small problem with the update statement since I have to query one table to get the values to populate another. There are PL/SQL issues in this type of UPDATE statement, but the magic mysterious keyword here is WHERE EXISTS.
Now, I worked for the company that makes the core application. I have supported this product now for the last 4 years. It has WHERE EXISTS all over it, but I never really understood it since I would only see fragments of code that used it within the product.
But a handy, dandy Google search on "Oracle updating a table from another" yielded this positively wonderful post that taught me what I needed to know.
UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
By using WHERE EXISTS (SELECT 1, etc, I get deterministic results, i.e. one record for each update. The problem I had before is that I would get many results back for a single update and that was no good.
The only BAD thing about this is that I end up having to update a PK value with itself. I am troubled by this, but at the moment, I don't see a way around it.
And people, let me tell you, that was only TODAY. Yesterday and most of this week was just as good.
1. I learned how to create date intervals I needed for specific conditions in DML. That's good because I couldn't code the DDL to work properly for historical record keeping. That's one made up requirement that could be tossed. (I assumed they wanted a historical record.)
2. I have a VBscript template for making an export to Excel. However, I couldn't get this to work for a week. I had once taken an VBscript template for an Access export and couldn't really get that to work either, so that's why this is a big deal for me.
3. In doing the VBscript project, I ended up shell scripting three specific things that build three specific tables that are exported. However, there is a process and order in which they must be run to get the data to come out correctly. I just think it's really nifty to be able to plan stuff logically like that. I like planning. I am a dork. I don't know. It just really came together for me this week. This has been stuff I've been pulling manually for the last 3 months and it feels really good to have focused time to really get it punched out and ignore everything else.
There are just too many projects to do and too many minor crises that have to be managed. It's terrible. Because it's so hard to get priorities aligned properly, I've been a tad unfocused.
But that's ok. We all know that this one manager makes work. She's very smart and a nice friend to have. There is a specific thing she's needed done for a long time and it was a challenge just testing the damned thing to make sure it was being done right. I never got it to work and other work kept bumping down my list. (My BFL! Yes, daruba! I use "BFL" at work!)
Well, 6 months later, I'm looking at the problem again, and have decided a whole new tactic. I am going to materialize a section of a view and then use that table to store the values I need to update. In doing so, I have a small problem with the update statement since I have to query one table to get the values to populate another. There are PL/SQL issues in this type of UPDATE statement, but the magic mysterious keyword here is WHERE EXISTS.
Now, I worked for the company that makes the core application. I have supported this product now for the last 4 years. It has WHERE EXISTS all over it, but I never really understood it since I would only see fragments of code that used it within the product.
But a handy, dandy Google search on "Oracle updating a table from another" yielded this positively wonderful post that taught me what I needed to know.
UPDATE TABLEA
SET (b, c, d) = (SELECT b1, c1, d1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
WHERE EXISTS (SELECT 1 from TABLEB WHERE TABLEB.a1 = TABLEA.a and TABLEB.e1 > 40)
By using WHERE EXISTS (SELECT 1, etc, I get deterministic results, i.e. one record for each update. The problem I had before is that I would get many results back for a single update and that was no good.
The only BAD thing about this is that I end up having to update a PK value with itself. I am troubled by this, but at the moment, I don't see a way around it.
And people, let me tell you, that was only TODAY. Yesterday and most of this week was just as good.
1. I learned how to create date intervals I needed for specific conditions in DML. That's good because I couldn't code the DDL to work properly for historical record keeping. That's one made up requirement that could be tossed. (I assumed they wanted a historical record.)
2. I have a VBscript template for making an export to Excel. However, I couldn't get this to work for a week. I had once taken an VBscript template for an Access export and couldn't really get that to work either, so that's why this is a big deal for me.
3. In doing the VBscript project, I ended up shell scripting three specific things that build three specific tables that are exported. However, there is a process and order in which they must be run to get the data to come out correctly. I just think it's really nifty to be able to plan stuff logically like that. I like planning. I am a dork. I don't know. It just really came together for me this week. This has been stuff I've been pulling manually for the last 3 months and it feels really good to have focused time to really get it punched out and ignore everything else.