Monet wrote:
I thought about this method before. But since there are 6 sets of rows I want to update in one table and they are common at some level, I am wondering whether there is more efficient way to do it.
Thanks,
From what you've told us, I don't think so.
By "common at some level," do you mean that they are similar in form? That is, you are doing the same update on each set, but what varies is the column names, Q1x, Q2x, ..., Q6x? I think the problem is that you are thinking like a programmer (normally a good thing) rather than a SQL coder. In a program, you might step through your data one row at a time, compare values to your conditionals, and branch into different operations based on that. With a db, however, we want to use indexes to avoid stepping through data one row at a time. That is, we can quickly find, and operate on, only those rows we want. In that case, as a programmer, what you want is 1 function called 6 times, one for each set.
Here's what I suggested before (option 1):
UPDATE temp
SET Q1E = 6, Q1F = 5, Q1G = 999
WHERE Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA')); UPDATE temp
SET Q2E = 6, Q2F = 5, Q2G = 999
WHERE Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN ('', 'NA'));Note how this parallels a function called twice, once for Q1 and once for Q2.
For comparison, here's how you could do that in 1 update (option 2):
UPDATE temp
SET
Q1E = IF(Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA')),6,Q1E)
Q1F = IF(Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA')),5,Q1F)
Q1G = IF(Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA')),999,Q1G)
Q2E = IF(Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN ('', 'NA')),6,Q2E),
Q2F = IF(Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN ('', 'NA')),5,Q2F),
Q2G = IF(Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN ('', 'NA')),999,Q2G)
WHERE (Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA')))
OR (Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND (Q2G IN ('', 'NA')));Aside from that being ugly (hard to read/maintain), I would be stunned if it weren't an order of magnitude slower than option 1, for two reasons:
* With proper indexes {probably one on (Q1A,Q1E,Q1F,Q1G) and one on (Q2A,Q2E,Q2F,Q2G)}, finding the matching rows in option 1 will be very fast. In option 2, however, using OR to combine compound conditionals using different columns will largely prevent using indexes. That is, mysql won't be able to pare down the possible rows very much using indexes, so it will have to compare the contents of the rows to the WHERE clause to find matches. This may even be a dreaded full table scan.
* In option 1, once we've found a matching row, we simply update it (set 3 column values). In option 2, we have to recompare the column values to determine which subgroup the row falls in, once for each column to be set, for a total of 6 times! Note that the only point of those comparisons is to divide the resultset into the 2 sets from option 1.
Now, if I understand you correctly, you actually have 6 sets of these, Q1 to Q6. Option 1 scales nicely into 6 simple, quick updates. Option 2 turns into an ugly monstrosity with a giant WHERE clause that will almost certainly force a full table scan, and 18 IF comparisons (3 per set) to be done for each matching row (with 15 out of 18 IFs resulting in "do nothing"). I'd bet the farm that option 1 will be much more efficient.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
