CODEWITHSUNDEEP
mysql
NineCattoRules
NineCattoRules

Reputation: 2428

How do you check multiple value for the same row in MySQL?

In MySQL I have these four tables:

quotes

id | uid | tid | message | time

quotes_rnd

id | message

users

idu | username

authors

id | uid | name

I need to populate the quotes table so this is my procedure:

BEGIN

INSERT INTO quotes
(message)
SELECT message
FROM quotes_rnd ORDER BY RAND() limit 1;

UPDATE quotes SET tid = (
SELECT id FROM authors WHERE id>10 ORDER BY rand() LIMIT 1)
WHERE tid = 0;

UPDATE quotes SET uid = (
SELECT idu FROM users WHERE idu BETWEEN 13 AND 63 ORDER BY rand() LIMIT 1)
WHERE uid = 0;

END

The problem

UPDATE quotes SET uid = (
SELECT idu FROM users WHERE idu BETWEEN 13 AND 63 ORDER BY rand() LIMIT 1)
WHERE uid = 0;

Before this query line, the tid value is already set, instead the uid=0.

How to SET uid only when uid.tid of all rows inside the table quotes is different from the one randomized here for the last row?

Upvotes: 1

Views: 82

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

Dear reader: The first two solutions fail with

MySQL: #1093 - You can't specify target table 'upd' for update in FROM clause"

I keep them here, though, as to make the problem known. Please scroll down to "EDIT" to see my third solution, which is working and is the one that's been accepted :-)

You say you want to try once with a random user and stay with uid = 0 in case of duplicate uid + tid. For this you'd use an UPDATE with a join:

UPDATE quotes upd 
  CROSS JOIN (SELECT idu FROM users WHERE idu BETWEEN 13 AND 63 ORDER BY rand() LIMIT 1) u
SET upd.uid = u.iud
WHERE upd.uid = 0
AND NOT EXISTS
(
  select *
  from quotes q
  where q.tid = upd.tid
  and q.uid = u.uid
);

If, however, you wanted to find a non-duplicating user as long as possible, you'd use a correlated subquery:

UPDATE quotes upd
SET uid = coalesce
(
  (
    SELECT idu 
    FROM users u
    WHERE idu BETWEEN 13 AND 63 
    AND NOT EXISTS
    (
      select *
      from quotes q
      where q.tid = upd.tid
      and q.uid = u.uid
    )
    ORDER BY rand() LIMIT 1
  ), 0
)
WHERE uid = 0;

The difference: You take all non-duplicating users into account. As long as there is at least one, you will update. Whereas with the first update statement, you'd just try just one random user, and either it's a non-duplicationg one then you'd update, else not.

EDIT: I am not fluent in MySQL yet, so I don't know how to overcome the MySQL limitation here quickly. If, however, you like the idea behind the second approach, then you might even want to strive to get tid/uid pairs as long as possible, i.e. don't update with a used-up tid when there are still tid/uid pairs for other tids available. You could do this all on insert:

INSERT INTO quotes (message, tid, uid)
SELECT q.message, a.id, coalesce(u.idu,0)
FROM quotes_rnd q
JOIN authors a ON a.id > 10 
LEFT JOIN users u ON u.idu BETWEEN 13 AND 63
  AND NOT EXISTS
  (
    select *
    from quotes q
    where q.tid = a.id
    and q.uid = u.idu
  )
ORDER BY (u.idu IS NULL), rand() LIMIT 1;

(This is kind of cross-joining in order to find all possible pairs, so it might be slow on large tables.)

SQL fiddle: http://www.sqlfiddle.com/#!9/7b756b/1.

Upvotes: 1

Related Questions