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.
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
