LibreOffice Base SQL: Update and Delete Records

 

While the INSERT statement is about creating records, the UPDATE statement is about changing data in an existing record. Therefore, unless you specify which record to change, the UPDATE will apply to all records.

NOTE: In my last post, we created the “TeamMember” table and inserted several records in it. If you haven’t read it yet, read it before you continue here, as we build on the data created in that post.

Update a Record

The template for the UPDATE statement looks like


UPDATE “TableName” SET “FieldName1” = value,... WHERE “FieldName2” = value;

With the UPDATE statement, you specify the field you want to change with the SET clause. You can define several fields to change in one statement by separating the “field = value” pairs with a comma. The last part of the statement is the WHERE clause. The WHERE clause specifies the record(s) to change. Without the WHERE clause, the statement would update the specified field(s) in every record in the table. This is rarely the desired affect.

Let’s do an example.

UPDATE “TeamMember”
SET “DOB” = '1995-5-1'
WHERE “ID” = 3;

This will update the “DOB” field on the record with an ID of 3. We don’t have to use the ID field to identify our records, and we can target multiple records as we will see.

The Mighty WHERE Clause

The WHERE clause is a diverse SQL option. The basic idea is comparing fields and values to determine a set of records. There are many comparison operators, not just equals “=”. Here is a chart of the comparisons operators.

Operator

Meaning

Sample

Sample Results

=

Equals

WHERE “ID” = 3

3

<>

Not Equal

WHERE “ID” <> 2

1, 3, 4…n

>

Greater Than

WHERE “ID” > 2

3, 4, 5…n

<

Less Than

WHERE “ID” < 3

0, 1, 2

>=

Greater Than

or Equal to

WHERE “ID” >= 2

2, 3, 4…n

<=

Less Than

or Equal to

WHERE “ID” <= 3

0, 1, 2, 3

You can combine more than one comparison using the AND and OR operators. When AND is used, both comparisons must evaluate to true for the WHERE to apply to a record. When OR is used, only one of the comparisons must evaluate to true for the WHERE to apply to a record.

Let’s do an example:

UPDATE “TeamMember”
SET “DOB” = '1985-10-23'
WHERE “FirstName” = 'Johnny'
AND
“LastName” = 'Walker';

Here we change the DOB for Johnny Walker. If we had only used the first name, this would have changed the DOB for Johnny Smith as well. If we had used OR instead of AND, it would have changed the DOB on Johnny Walker, Johnny Smith, Sandy Walker, and Henry Walker. So, you can see how picking the right values and operators can greatly change the outcome.

Now, let’s do something a little more complex. Let’s say that Johnny and Sandy Walker are married, but the last name was entered incorrectly. Their last name is actually Walkers. So, we need to change the last name for only Johnny and Sandy. Here is the SQL:

UPDATE “TeamMember”
SET “LastName” = “Walkers”
WHERE “LastName” = 'Walker'
AND
(“FirstName” = 'Johnny'
OR
“FirstName” = 'Sandy');

Notice that we grouped the two first name comparisons together with parentheses. This is necessary to have the results come out correctly. Without the parentheses, the command would try to change the last name for Sandy Smith as well. You can think about it mathematically. Anything in parentheses is done first or logically grouped together. Therefore, because of the parentheses, we only get the records where the last name is Walker, and the first name is either Johnny or Sandy.

Deleting Records

The DELETE command is powerful, and you can do a lot of destruction with one single command. For example

DELETE FROM “TeamMember”;

deletes all the records from the table. This isn’t usually the result you want. Once again, we add a WHERE clause to specify which record(s) we want to delete. So, if Johnny Smith leaves the team, we can delete him with the following command

DELETE FROM “TeamMember”
WHERE “FirstName” = 'Johnny'
AND
“LastName” = 'Smith';

Once again, without the AND, we would have deleted Johnny Walkers as well.

Well, that’s all for updating and deleting records. When I get to it, we will discuss creating queries with SQL. This will require a few posts, and you will learn even more about using the full power of the WHERE clause.

 

 

Advertisements

3 comments on “LibreOffice Base SQL: Update and Delete Records

  1. Karl says:

    I’m trying to copy multiple rows of a specific column from one table to another table by using update.
    So far I have no luck with it.


    update tParts
    set tParts.Container = tOrderParts.Container
    from
    tParts join tOrderParts
    on tParts.InternalName = tOrderParts.InternalName
    where tOrderParts.`Order-ID` = 1
    ;

    Do you have any idea, why this isn’t working?

    • Karl says:

      PS: the error message says: “1: Table not found in statement [update tParts]”

    • E E Perry says:

      You wouldn’t use UPDATE, but SELECT, as in

      SELECT “Field1”, “Field2” INTO “New_Table” FROM “My_Table”;

      You have to run this from the SQL tool rather than the Query dialog because it creates a table.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s