CODEWITHSUNDEEP
mysql
Björn C
Björn C

Reputation: 4008

mySQL: Where should i put my WHERE?

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;

Upvotes: 0

Views: 44

Answers (2)

Lepanto
Lepanto

Reputation: 1413

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

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521219

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.

Upvotes: 1

Related Questions