MySQL UPDATE AND INSERT Statement

Question

Currently there's some data that was entered incorrectly and I basically need to run a schema which updates the current record and inserts a new one (in one statement if possible). The table is set up as so:

  cityID            int(10)
  stateID           smallint(5)
  orderEnterpriseID int(10)
  isDefault         tinyint(3)
  locationType      tinying(2)

Right now, every record has a locationType of 0 and I need to update it to 5:

UPDATE
table SET
table.locationType = 5 WHERE table.locationType = 0 AND table.orderFromEnterprise = 0;

But I also need to create another record with duplicate data and set the locationType to 6. I have issues wrapping my head around some of these SQL statements and any help is greatly appreciated!!!!

I think what you're really looking for is a transaction, not a single statement. You can't do that as one statement. — Jeff Ferland
– Jeff Ferland, Commented Mar 31, 2011 at 19:29
@Jeff Ferland yes i agree, if anything a transaction would serve the purpose, also pointed that out in my answer. — ntziolis
– ntziolis, Commented Mar 31, 2011 at 19:34

ntziolis · Accepted Answer · 2011-03-31 19:34:54Z

1

First execute the update as you described already:

UPDATE
table SET
table.locationType = 5 WHERE table.locationType = 0 AND table.orderFromEnterprise = 0;

Then copy all records and assign them a 6 as location type on the insertion, remember to limit the records by locationType = 5, to make sure that newly added records are not copied as well (not sure if thats even an issue with mysql but just to be sure):

INSERT INTO table 
(cityID, stateID, orderEnterpriseID, isDefault, locationType) 
SELECT t1.cityID, t1.stateID, t1.orderEnterpriseID, t1.isDefault, 6 
FROM table as t1
WHERE t1.locationType = 5

Its not in one statement but it will get the job done, if you're worried about inconsistencies then just wrap a transaction around the two operations.

edited Mar 31, 2011 at 19:34

answered Mar 31, 2011 at 19:16

ntziolis

10.2k1 gold badge36 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dmitry Avtonomov · Accepted Answer · 2011-03-31 19:17:25Z

1

you can't do that within the UPDATE statement, update only updates what already is in the table.

if you want to duplicate your entries changing one field you could do the following:

INSERT table_name (cityID, stateID , orderEnterpriseID, isDefault, locationType)
SELECT cityID, stateID , orderEnterpriseID, isDefault, 6
FROM table_name

(please note, that all of this is executed as one statement) also note that we select 6 instead of locationType from the table.

What we do here is simply select everything from the table substituting location type to 6 and inserting this back into the table.

answered Mar 31, 2011 at 19:17

Dmitry Avtonomov

9,6494 gold badges37 silver badges49 bronze badges

3 Comments

ntziolis Over a year ago

I would recommend limiting the select with a where clause to get rid of any confusion that might arise since you insert rows into the same table, not sure what the server will do, I would actually expect it to keep coping the newly inserted rows as well, but the query execution might also already be complete when the insert starts, not sure how mysql handles that, therefore its better to limit the select ;)

Dmitry Avtonomov Over a year ago

i've checked it before posting it here, everything works as it should. these types of statements wouldn't be there in the sql at all if such race conditions were possible. i'm not even sure it is at all possible to create a race condition by issuing an sql query.

ntziolis Over a year ago

not saying that it doesn't, just prefer to always be on the safe side ;)

Ohas · Accepted Answer · 2011-03-31 19:18:14Z

1

I don't think it is possible to do this in a single query. You can, however, insert duplicate rows with locationType set to 6 and then update locationTypes of 0 to 5. Here are the queries:

insert into table (cityID, stateID, orderEnterpriseID, isDefault, locationType)
select cityID, stateID, orderEnterpriseID, isDefault, 6
from table
where locationType = 0 and orderEnterpriseID = 0;

# now your update query
update table
set locationType = 5
where locationType = 0 and orderEnterpriseID = 0;

answered Mar 31, 2011 at 19:18

Ohas

1,8714 gold badges22 silver badges29 bronze badges

Collectives™ on Stack Overflow

MySQL UPDATE AND INSERT Statement

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related