I have a MySQL table that looks like this, where pKey is a primary_AI Key.
------ ----------- ---------- ----------
| pKey | firstName | LastName | Username |
------ ----------- ---------- ----------
| 1 | John | Brown | jBrown |
| 2 | Jake | Smith | jSmith |
| 3 | Mary | Laurier | mLaurier |
------ ----------- ---------- ----------
I want to create a query where it can replace the LastName attribute when given a firstName. For example, when firstName of John is supplied, for example, I want to change the last name to Wilson.
I wrote this query to replace (or automatically create the row if not exists) the last name when supplied a first name.
REPLACE into `myTableName` (LastName, Username) VALUES ("Wilson", "jWilson") WHERE firstName = "John";
I'm expecting this query to change the record of John Brown to John Wilson and the username of jBrown to jWilson. If the row that has the first name of John doesn't exist, I also want it to create a new row with the value of John as the firstName and Wilson as the LastName as well as changing the username to jWilson.
I know that I can do this with multiple queries, such as SELECT the first name, INSERT if it does not exist, or DELETE the record and INSERT it again if it exists, but I have many records I need to insert at once and want to do this in a single query.
How can I fix this?
CodePudding user response：
To avoid duplicate usernames, you should use:
alter table `myTableName` modify column username char(64) unique;
To replace (delete then update) into the table
REPLACE into `myTableName` (Username) VALUES (CONCAT(SUBSTR(firstname,0,1),lastname))) WHERE firstName = "John";
REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
Once it works the way you want it to, remove the WHERE
You can also use INSERT ON DUPLICATE UPDATE
insert into user (firstname,lastname,username) values ("frank","our",lcase(concat(substr(firstname,1,1),lastname))) ON DUPLICATE KEY UPDATE username=lcase(concat(substr(firstname,1,1),lastname));
If you specify an ON DUPLICATE KEY UPDATE clause and a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row occurs.
To update the existing values, use an UPDATE
UPDATE `myTableName` SET username = LCASE(CONCAT(SUBSTR(firstname,0,1),lastname))) WHERE firstName = "John";
CodePudding user response：
A kind of hacky way to do this since you don't want to add the unique constraint to the first_name column even though you're sure it's gonna be unique would be to get the pKey of the row where firstName = 'John', and if that doesn't exist, use 0 as pKey instead to let mysql get the next value for you:
INSERT INTO table_name
select (coalesce((select pKey from table_name where firstName = 'John'),
0)) as pKey,
'John' as firstName,
'Wilson' as lastName,
'jWilson' as userName
on duplicate key update lastName = 'Wilson', userName = 'jWilson';