MySQL UPDATE AND INSERT Statement

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开发者_JAVA技巧 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!!!!

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.

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.

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;