Using 'WHERE' with user-defined variable

Question

I'm fairly new to SQL, and I'm trying save myself some time searching for a single value across multiple columns, so that I don't have to paste the value into 3 quotes.

EG:

SET @u1 := '000123';

SELECT * FROM database1.table1
WHERE id = @u1
OR prev_id = @u1
OR next_id = @u1
OR link_id = @u1
;

So in the above scenario, I'm trying to find where the id=000123 is either the id, prev_id, next_uid, link_uid, rather than having to paste 4 instances in the following:

SELECT * FROM database1.table1
WHERE id LIKE '000123'
OR prev_id = '000123'
OR next_id = '000123'
OR link_id = '000123'
;

Looking around, I'm fairly sure that this is possible, but perhaps I'm missing something?

Gordon Linoff · Accepted Answer · 2014-03-28 12:01:09Z

1

Is this what you are looking for?

SELECT *
FROM database1.table1
WHERE  @u1 in (id, prev_id, next_id, link_id);

edited Mar 28, 2014 at 12:01

answered Mar 28, 2014 at 11:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

n8udd Over a year ago

It may well be, but I'm receiving the following error message: Error Code: 1270. Illegal mix of collations (utf8_general_ci,IMPLICIT), (utf8_unicode_ci,IMPLICIT), (utf8_unicode_ci,IMPLICIT) for operation ' IN '

Gordon Linoff Over a year ago

It sounds like the default database collation is different from the table collation. You can use the COLLATE key work to change collations.

Collectives™ on Stack Overflow

Using 'WHERE' with user-defined variable

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related