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,
--- Michael Stassen <[EMAIL PROTECTED]>
wrote:
> 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.
>
>
_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
