New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

In SQL do updates accumulate or overwrite? #36

Open
scripting opened this Issue Oct 11, 2017 · 14 comments

Comments

Projects
None yet
8 participants
@scripting
Owner

scripting commented Oct 11, 2017

Suppose I have table with three fields -- id, weight, when
	we're keeping track of people and their weight.
	id identifies a person
	weight is their weight
	when is when the reading took place
I enter the weight every week
	when I do that, does that create a new row in the table, or does it replace the existing row?
I have a feeling it has to do with keys
	I don't even remember if SQL databases have keys!
	So this is a crucial question.
	If they do have keys, I'd make _id_ the key.
	Note I _want_ it to overwrite. I don't care what the person's weight was last week or year. 
@jonsagara

This comment has been minimized.

Show comment
Hide comment
@jonsagara

jonsagara Oct 11, 2017

Yes, updates overwrite.

Assuming id is the primary key in the Weights table, the very first time this person weighs him/herself, you'd

INSERT INTO Weights(id, weight, when) VALUES (1, 150.0, 'measurement-date-here')

To overwrite on subsequent measurements for the same person, you'd

UPDATE Weights SET weight = 150.5, when = 'new-date-here' WHERE id = 1

jonsagara commented Oct 11, 2017

Yes, updates overwrite.

Assuming id is the primary key in the Weights table, the very first time this person weighs him/herself, you'd

INSERT INTO Weights(id, weight, when) VALUES (1, 150.0, 'measurement-date-here')

To overwrite on subsequent measurements for the same person, you'd

UPDATE Weights SET weight = 150.5, when = 'new-date-here' WHERE id = 1

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 11, 2017

OK - I'm going to try and keep this simple.

Overwrite or Update depends upon whether you do an UPDATE (overwrite) or INSERT (new row)

That answers your question, but there is more implied - see below

jswright61 commented Oct 11, 2017

OK - I'm going to try and keep this simple.

Overwrite or Update depends upon whether you do an UPDATE (overwrite) or INSERT (new row)

That answers your question, but there is more implied - see below

@sivabalans

This comment has been minimized.

Show comment
Hide comment
@sivabalans

sivabalans Oct 11, 2017

UPDATE table weights set weight = "some_weight" where id = "some_id" - Always updates the row.
INSERT table weights (id, weight, when) - Always inserts a row.
So when updating the weight of a person by supplying an existing ID, it should update the row.
If you try to update the weight of a person by supplying a non-existent ID, it should throw a SQL error.
If you try to insert the weight of a person with a non-existent ID, it should insert a new row
If you try to insert a weight of person with an existing ID, and if that column has a Unique index(which it should), it should throw a SQL error.

sivabalans commented Oct 11, 2017

UPDATE table weights set weight = "some_weight" where id = "some_id" - Always updates the row.
INSERT table weights (id, weight, when) - Always inserts a row.
So when updating the weight of a person by supplying an existing ID, it should update the row.
If you try to update the weight of a person by supplying a non-existent ID, it should throw a SQL error.
If you try to insert the weight of a person with a non-existent ID, it should insert a new row
If you try to insert a weight of person with an existing ID, and if that column has a Unique index(which it should), it should throw a SQL error.

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 11, 2017

You probably want a 'person' table, with an id which is its primary key.

Then a weights table with an id as primary key, and a person_id as a foreign key.

jswright61 commented Oct 11, 2017

You probably want a 'person' table, with an id which is its primary key.

Then a weights table with an id as primary key, and a person_id as a foreign key.

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 11, 2017

Owner
Owner

scripting commented Oct 11, 2017

@sivabalans

This comment has been minimized.

Show comment
Hide comment
@sivabalans

sivabalans Oct 11, 2017

sivabalans commented Oct 11, 2017

@facej

This comment has been minimized.

Show comment
Hide comment
@facej

facej Oct 11, 2017

Depending on your database. In Postgres you might use "UPSERT". In MySQL you might use "INSERT ... ON DUPLICATE KEY UPDATE"

facej commented Oct 11, 2017

Depending on your database. In Postgres you might use "UPSERT". In MySQL you might use "INSERT ... ON DUPLICATE KEY UPDATE"

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 11, 2017

Or you can use replace instead of insert or update
Check out the replace syntax, https://dev.mysql.com/doc/refman/5.5/en/replace.html

Replace does an insert if the row with the primary key does not exist, but it deletes the old row if the key existed and then inserts the new row - saves a database trip.

You end up with a table that stores the current weight for each person, but no history.

jswright61 commented Oct 11, 2017

Or you can use replace instead of insert or update
Check out the replace syntax, https://dev.mysql.com/doc/refman/5.5/en/replace.html

Replace does an insert if the row with the primary key does not exist, but it deletes the old row if the key existed and then inserts the new row - saves a database trip.

You end up with a table that stores the current weight for each person, but no history.

@jswright61

This comment has been minimized.

Show comment
Hide comment
@jswright61

jswright61 Oct 11, 2017

If you want history for weight, you might use a structure like this:

CREATE TABLE people (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT NULL,
dob date DEFAULT NULL,
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE weights (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
person_id int(10) UNSIGNED DEFAULT NULL,
weight decimal(5,2) DEFAULT NULL,
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES people (id),
INDEX person_id USING BTREE (person_id) comment ''
) ENGINE=InnoDB AUTO_INCREMENT=1;

jswright61 commented Oct 11, 2017

If you want history for weight, you might use a structure like this:

CREATE TABLE people (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT NULL,
dob date DEFAULT NULL,
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1;

CREATE TABLE weights (
id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
person_id int(10) UNSIGNED DEFAULT NULL,
weight decimal(5,2) DEFAULT NULL,
created_at datetime DEFAULT NULL,
updated_at datetime DEFAULT NULL,
PRIMARY KEY (id),
CONSTRAINT fk_person FOREIGN KEY (person_id) REFERENCES people (id),
INDEX person_id USING BTREE (person_id) comment ''
) ENGINE=InnoDB AUTO_INCREMENT=1;

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 11, 2017

Owner

Or you can use replace instead of insert or update

BING!

Owner

scripting commented Oct 11, 2017

Or you can use replace instead of insert or update

BING!

@TomLoyal

This comment has been minimized.

Show comment
Hide comment
@TomLoyal

TomLoyal Oct 11, 2017

Dave,

Not that I ever ran into this in working with SQL :) , but some programmers I know have...

Thou shalt always use a where clause when doing an update, lest all rows in the table be overwritten.
Omitting columns in your REPLACE column list will cause nulls to overwrite your existing data unless those columns do not allow nulls.

Keep calm and recover with backups :)

TomLoyal commented Oct 11, 2017

Dave,

Not that I ever ran into this in working with SQL :) , but some programmers I know have...

Thou shalt always use a where clause when doing an update, lest all rows in the table be overwritten.
Omitting columns in your REPLACE column list will cause nulls to overwrite your existing data unless those columns do not allow nulls.

Keep calm and recover with backups :)

@scripting

This comment has been minimized.

Show comment
Hide comment
@scripting

scripting Oct 11, 2017

Owner
Owner

scripting commented Oct 11, 2017

@easp

This comment has been minimized.

Show comment
Hide comment
@easp

easp Oct 11, 2017

Something to be aware of: in the case of doing a replace or update, where there could be a concurrent request reading or writing the same row, you may find yourself bumping into how the SQL database you are using handles concurrency and transaction isolation. Postgres defaults to (potentially) slow, but safe. I don't know about MySQL, but in the past, it was generally rather reckless.

easp commented Oct 11, 2017

Something to be aware of: in the case of doing a replace or update, where there could be a concurrent request reading or writing the same row, you may find yourself bumping into how the SQL database you are using handles concurrency and transaction isolation. Postgres defaults to (potentially) slow, but safe. I don't know about MySQL, but in the past, it was generally rather reckless.

@danderson3

This comment has been minimized.

Show comment
Hide comment

danderson3 commented Oct 15, 2017

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment