CODEWITHSUNDEEP
mysqlsqldatabase
Prince
Prince

Reputation: 1192

Update the table of a database

I have a database containing this table: Table

The table is made up of 3 columns: -id :identify each row of the table -address: keep all the addresses -default: a Boolean column where 1 defines the default address used by the user

On the image above the default address used is address1

I would like to add a fourth address and set that fourth address to default. So I wrote the following lines:

-UPDATE address SET default=false
-INSERT INTO addresses SET address = `address4`, default=true

What I would like to know is that, is there any way to combine the 2 lines to improve performance? Or using the 2 lines is ok ?

Thanks in advance for providing your help

Upvotes: 1

Views: 42

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

You cannot combine them into a single statement. You can, however, combine them into a single transaction so they take effect at the same time.

You do this with start transaction, which is documented here.

Also, I would suggest that you reverse the operations:

INSERT INTO addresses
    SET address = $address4,
        default = false;

UPDATE address
    SET default = (case when address = $address4" then true else false end);

This looks more complicated. But, if you are not using transactions ensures that the default address is always set for other processes querying the table (assuming the storage engine has some ACID capabilities).

Upvotes: 3

Related Questions