CODEWITHSUNDEEP
mysqlsql-update
Rakesh KR
Rakesh KR

Reputation: 6527

Update table in SQL

I need to update two tables, I just try following two cases.

case 1: Update the two tables in one single query as,

UPDATE tableName1,tableName2 
SET    tableName1.column1 = 12, 
       tableName2.column1 = 12 
WHERE  tableName1.column2 = 13 
AND    tableName2.column2 = 13;

case 2: Update by two separate queries as,

UPDATE tableName1 
SET    column1 = 12 
WHERE  column2 = 13;

UPDATE tableName2 
SET    column1 = 12 
WHERE  column2 = 13;

Here case 1 take more time to execute the query as compared to case 2 why?
And which is the better method to do this operation?

Upvotes: 1

Views: 75

Answers (4)

Chamil
Chamil

Reputation: 803

In the first way, the database engine should do a join in order to find the particular records to be updated. in the second way, its just two different updates on two different table, the time, resources to do the 2 table join is saved. hence the second way is more preferred.

Upvotes: 1

Jonathan Leffler
Jonathan Leffler

Reputation: 753870

The first UPDATE is pointlessly complex.

The pair of independent UPDATE operations is able to make full use of indexes on Column2 in each table (if they're present), and will generally be much swifter.

Upvotes: 1

Anoop
Anoop

Reputation: 369

If you are using a Stored Procedure you can use separate queries and yet need to pass the values in to the procedure as parameters. It is better to use the second method if there is no special case such as both the tables need to be updated at the same time etc.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

You are using a Cartesian join of the two tables drastically increasing the number of rows being evaluated on a table of any decent size. Specify a join condition on indexed fields to resolve this.

These are also not equivalent queries in their functionality.

Upvotes: 1

Related Questions