mySQL: Where should i put my WHERE?

Question

I'm trying to add a WHERE clause.
How should i manage it?

This is my Query:

UPDATE  usr_time_reg
JOIN users  ON usr_time_nr = usr_time_reg.usr_time_nr
SET usr_time_reg.usr_employment = users.employment;

I'm trying to add: WHERE usr_time_reg.usr_emplyoment = "Övrigt"

I've tried:

UPDATE usr_time_reg
JOIN users  ON usr_time_nr = usr_time_reg.usr_time_nr, 
usr_time_reg.usr_employment = "Övrigt"
SET usr_time_reg.usr_employment = users.employment;

UPDATE usr_time_reg
JOIN users ON usr_time_nr = usr_time_reg.usr_time_nr
SET usr_time_reg.usr_employment = users.employment
WHERE usr_time_reg.usr_employment = "Övrigt";

UPDATE usr_time_reg
JOIN users ON usr_time_nr = usr_time_reg.usr_time_nr
WHERE usr_time_reg.usr_employment = "Övrigt"
SET usr_time_reg.usr_employment = users.employment;

UPDATE usr_time_reg
JOIN users ON usr_time_nr = usr_time_reg.usr_time_nr
AND usr_time_reg.usr_emplyment = "Övrigt"
SET usr_time_reg.usr_employment = users.employment;

UPDATE A SET foo = B.bar FROM TableA A JOIN TableB B ON A.col1 = B.colx WHERE ... — Devsi Odedra, May 28 '18 at 13:23
Your first proposal is ok, you just need to put the keyword WHERE in front of it. — tangoal, May 28 '18 at 13:23
more information on [this](http://www.mysqltutorial.org/mysql-update-join/) website — Lepanto, May 28 '18 at 13:27
@tangoal I get this error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE usr_time_reg.usr_employment = "Övrigt" SET usr_time_reg.usr_employment =' at line 3 — Björn C, May 28 '18 at 13:27
Your third query looks correct to me. Did you try running it? Did you [read this answer before you posted](https://stackoverflow.com/questions/15209414/how-to-do-3-table-join-in-update-query) ? — Tim Biegeleisen, May 28 '18 at 13:27
I guess that the Syntax error has nothing to do with the placement of the WHERE. But I don't see any syntax error at the moment. Ok, I'm on my phone and I might oversee something. — tangoal, May 28 '18 at 13:31
@TimBiegeleisen i get this error: Column 'usr_time_nr' in on clause is ambiguous — Björn C, May 28 '18 at 13:32

Lepanto · Accepted Answer · 2018-05-28T13:43:40.430

1

Try below as it missing table reference in ON

UPDATE usr_time_reg
JOIN users ON users.usr_time_nr = usr_time_reg.usr_time_nr
SET usr_time_reg.usr_employment = users.employment
WHERE usr_time_reg.usr_employment = "Övrigt";

With JOIN

UPDATE usr_time_reg as tr, users as ur
SET tr.usr_employment = ur.employment
WHERE ur.usr_time_nr = tr.usr_time_nr and tr.usr_employment = "Övrigt";

While joining tables add table alias before field name to avoid ambiguous column error

edited May 28 '18 at 13:43

answered May 28 '18 at 13:35

Lepanto

1,413
1
8
15

Thank you! Works very well! – Björn C May 28 '18 at 13:41

score 1 · Answer 2 · answered May 28 '18 at 13:35

The following query should work:

UPDATE usr_time_reg u1
INNER JOIN users u2
    ON u1.usr_time_nr = u2.usr_time_nr
SET u1.usr_employment = u2.employment;
WHERE u1.usr_emplyment = 'Övrigt';

It can be difficult to keep track of update join syntax for MySQL, in addition to other databases you might be using. So it's always a good idea to have a good reference to use in case you forget.

The main change I made, other than using correct syntax, was to introduce table aliases to the update query. In addition to making it a lot easier to read, it also resolved one of your errors regarding an ambiguous column reference to user_time_nr. Now, it is clear to which table we are referring when we use that column.

mySQL: Where should i put my WHERE?

2 Answers2