-1

Currently I have a db wich has a table called "users". In that table I have column "id" as PRIMARY and AUTO_INCREMENT. In php, when I want to update a user's balance I just say:

UPDATE `users` SET `balance` = '500' WHERE `id` = 3;

The thing is that if I go to phpmyadmin and I manually edit the column, it returns this query:

UPDATE `users` SET `balance` = '500' WHERE `users`.`id` = 3;

So, what's the difference between id and users.id?

Grizzlly
  • 486
  • 3
  • 14
  • 3
    no difference .. is an implict or esplicit naming for the column name – ScaisEdge Jan 15 '18 at 21:01
  • 1
    The second, `'users'.'id'`, is more specific. It's the same query – Coder-guy Jan 15 '18 at 21:01
  • ^^, in this particular case. its useful syntax when you start playing with multiple tables and multiple db's all at once –  Jan 15 '18 at 21:02
  • No difference at all. You have to use the table name when you're using multiple tables, but you don't need to specify it when you're using a single table. – aynber Jan 15 '18 at 21:02
  • It is just being verbose and specifying to remove ambiguity. Consider queries where more than one table has the same column: `SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;` You need to always specify which `id` you want. – AbraCadaver Jan 15 '18 at 21:03

1 Answers1

4

In this case, there isn't one. The "users." portion of "users.id" just identifies that it comes from the table "users." If you had more than one table in your query (which is very unlikely with an UPDATE statement), and both of those tables had an id column, the "users." identifier would be very important. Since you're only using one table, it's simply personal preference whether it is included.

Hope this helps!

Karen B
  • 56
  • 2