Re: Question about Update multiple columns at once

[Michael Stassen]

Assuming that column Q3F in your example is really Q2F, the query is doing 
exactly what you told it to do.  Rows which match your WHERE clause are 
being updated according to your SET clause.

As I understand you, there are two different sets of rows you wish to 
update.  The first set are the rows which match

  Q1A = 1 AND Q1E = 1 AND Q1F = 1 AND (Q1G IN ('', 'NA'))
while the second set are the rows which match
  Q2A = 1 AND Q2E = 1 AND Q2F = 1 AND Q2G IN ('', 'NA'))
If you wanted to do the same thing to both sets, one update query would make 
sense, but you don't.  You want to make one set of changes to the first set 
of rows, and a different set of changes to the second set of rows.  I think 
that calls for two updates:

  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 that, as a bonus, doing it this way eliminates the need for the IFs in 
the SET clauses, because their conditions are guaranteed to be met by rows 
which match the WHERE clauses.

Michael
Monet wrote:
I’m trying to update multiple columns at once but
cann’t do it in an efficient way.
What I am trying to do is:
Update table temp, 
When: 	( Q1A=1 AND Q1E=1 AND Q1F=1 AND Q1G IN
(‘’,”NA”) ) THEN SET (Q1E=6,Q1F=5, Q1G=999),
OR When ( (Q2A=1 AND Q2E=1 AND Q2F=1 AND Q2G IN
(‘’,’NA’) THEN SET (Q2E=6,Q2F=5,Q2G=999))

The record falls in one of above cases should be
updated. 
The query I used is:
UPDATE temp
	SET Q1E = IF(Q1E = 1, 6, Q1E),
	Q1F = IF(Q1F = 1, 5, Q1F),
	Q1G = IF(Q1G IN ('','NA'),999, Q1G),
	Q2E = IF(Q2E = 1, 6, Q2E),
	Q2F = IF(Q2F = 1, 5, Q2F),
	Q2G = IF(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'));
         
This query has problem. It also updates the record
like following:
Before update: Q1A=1,Q1E=1,Q1F=1,Q1G=’NA’, Q2A=1,
Q2E=3, Q3F=1;
After update: Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1, Q2E=3,
Q3F=5.
However, since Q2E=3, this record should be:
Q1A=1,Q1E=6,Q1F=5,Q1G=999, Q2A=1, Q2E=3, Q3F=1.

So, any suggestion?
Thanks a lot.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]