CODEWITHSUNDEEP
mysqlsql
Sammi De Guzman
Sammi De Guzman

Reputation: 567

How to update "sub-fields" from a string field?

I am working with a database imported into MySQL from a QuickBooks file. Due to the design of the QB database, I have a field that looks like this, but with different fields:

First Name: Sammi
Last Name: Fernandez
DOB: 1997
Hair: Black

in a single field. The task is, to separate those into the sub-fields, and UPDATE the table to place each one in its own column.

The SELECT statement works great:

SELECT
    TRIM(
        SUBSTR(description,
            LOCATE("First Name:", description) + LENGTH("First Name:"),
            LOCATE("Last Name:", description) - (LOCATE("First Name:", description) + LENGTH("First Name:"))))
        AS FirstName,
    ...
    FROM [table];

but when I try to UPDATE the table,

UPDATE [table] SET FirstName =
    TRIM(
        SUBSTR(description,
            LOCATE("First Name:", description) + LENGTH("First Name:"),
            LOCATE("Last Name:", description) - (LOCATE("First Name:", description) + LENGTH("First Name:")))),
    ...
WHERE 1;

I get something along the lines of

0 rows affected

which means it's not really setting anything right?

Upvotes: 0

Views: 62

Answers (1)

doublesharp
doublesharp

Reputation: 27599

You are correct, it is not updating anything. The WHERE 1 on your update is preventing it from matching any rows, so none are updated. Remove this and it should update the FirstName field for all records in the table - use a WHERE if you only want to update some of the rows.

Upvotes: 1

Related Questions