INNER JOIN but not editing rows

Question

I'm trying to set the adminid of CTBan_Log to the value of sb_admins aid (admin id) but only where the steamid matches the authid of the row

UPDATE CTBan_Log ct 
    INNER JOIN sb_admins s ON
               ct.adminid = s.aid
    INNER JOIN sb_admins st ON 
               ct.admin_steamid = st.authid
SET adminid=s.aid WHERE admin_steamid=st.authid

this executes but doesn't edit any rows. What am I doing wrong?

As Gordon said in the answer, well not that he said it, but it does not appear that you need an alias of a self-join because you have no criteria or OP description of why you need such of a thing. — Drew, Oct 25 '16 at 20:48
The referenced dupe close may bring in more tables but it is a canonical reference. Use accordingly. — Drew, Oct 25 '16 at 20:50
Doesn't really look like it should ever actually change anything. Specifically, the first join condition insures the SET will be effectively a "no op". — Uueerdo, Oct 25 '16 at 21:00

score 0 · Accepted Answer · answered Oct 25 '16 at 20:44

0

Your description suggests this logic:

UPDATE CTBan_Log ct INNER JOIN
       sb_admins s 
       ON ct.adminid = s.aid AND
          ct.admin_steamid = st.authid
    SET ct.adminid = s.aid ;

answered Oct 25 '16 at 20:44

Gordon Linoff

1,242,037
58
646
786

Thanks, but it still doesn't edit any rows – Joram Oct 25 '16 at 21:00
@Joram . . . Then either the `join` conditions don't match any rows or the `adminid` doesn't actually change value. – Gordon Linoff Oct 26 '16 at 02:13

INNER JOIN but not editing rows

1 Answers1