0

Respected Member

my first table company has following fields

  • com_id
  • p_id
  • p_name

my second table users has following fields

  • p_id
  • p_name
  • role_def
  • com_id

Question

I want to update multiple record on both tables with below query as example.

Please guide as it is not being working

UPDATE company,
       users
SET company.p_name='Javeria Rauf',
    users.p_name='Javeria Rauf',
    users.role_def='Admin'
WHERE company.p_id=9
  OR users.p_id=9
Ankit Jindal
  • 3,672
  • 3
  • 25
  • 37
  • You can't update two tables at once in one statement like that; it's just not something SQL can do. What is the actual problem you're trying to solve by combining them like that? – IMSoP Mar 18 '21 at 17:57
  • The SQL table structure as described, has a few _code smells_; duplicate and unclear column names, hence non-intuitive query solutions. The `company/user/role model` is the most commonly used relation (in multi-user applications); many good examples can be found. The root of the problems stem from violations of _SQL normal forms_. Skipping details, the _users table_ should have columns: id, name, role. The user_company table is a _join table_ and should have columns (that are foreign keys): user_id, company_id. Now, a single query suffices. For multiple roles, add a user_role join table. – Jeff Sheffel Mar 19 '21 at 17:28

1 Answers1

0

Just use two separate update statement as below:

To update company:

UPDATE company SET company.p_name = 'Javeria Rauf' WHERE company.p_id = 9 ;

To update users:

UPDATE users SET users.p_name = 'Javeria Rauf', users.role_def='Admin' WHERE users.p_id = 9 ;
WOUNDEDStevenJones
  • 5,150
  • 6
  • 41
  • 53
Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26