using IF (I think) 1

[azzazzello]

I have 2 tables, one (call it F_table) with 5 fields, other (call it D_table) with 4 fields.

I have a statement

REPLACE INTO D_TABLE (a,b,c,d) SELECT (a,(if b in {3,5} use b, otherwise, use x),c,d FROM F_TABLE WHERE ...

I am not exactly sure how to embed that IF statement in there...basically, I want to copy over a,c,d fields, but as for D_TABLE.b, I want to copy into it from F_TABLE.b if b is 3,4, or 5, otherwise I want to copy into it from F_TABLE.x

 

[TonyGroves]

How about:
[tt]
REPLACE d_table (a,b,c,d)
SELECT a,IF(b IN (3,5),b,x),c,d
FROM ...
[/tt]

 

[TonyGroves]

Sorry, that should be:[tt]
IF(b IN (3,4,5),b,x)[/tt]
or:[tt]
IF(b BETWEEN 3 AND 5,b,x)[/tt]

 

[azzazzello]

thanx - can you tell I never used IF before?

BTW I found a 5.0.7 bug while at it. Apparently LIKE is broken. I did this query

SELECT * from MYTABLE WHERE somefield NOT LIKE 'nonsense%';

somefield has no null values. nor does it have 'nonsense'. Yet above query returned empty set. So did removing the NOT. However when identical query was run on identical data on 4.1.7, it returned the proper result set.

 

[TonyGroves]

Hmm ... you wouldn't really expect new bugs to be cropping up in such well-established features as LIKE, but if the evidence is there ...

I wonder are MySQL offering "bug bounties", like (I think) Mozilla?

 

[azzazzello]

interesting thing is,

this doesn't work

SELECT * from MYTABLE WHERE somefield NOT LIKE 'nonsense%';

but this does!

SELECT * from MYTABLE WHERE somefield NOT LIKE '%nonsense%';

 

[r937]

it's beta, i wouldn't use it yet

r937.com | rudy.ca