Need to use UPDATE instead of INSERT INTO

[phlat]

I need to UPDATE exisiting rows and populate
the fields using only the resources in this INSERT statement.

INSERT INTO cms.project_event (projectPhaseID,sortOrder) SELECT
cms.projectTemplate_eventTemplate.projectTemplatePhaseID,
cms.projectTemplate_eventTemplate.sortOrder
FROM cms.projectTemplate_eventTemplate WHERE
cms.projectTemplate_eventTemplate.projectTemplateID = '1'

Any clue pointing in the right direction will be of great help. Im not providing alot to go on just the to update the two fields from the other table instead of INSERTING new rows.

Thanks!

 

[ericbrunson]

Try "replace into" instead of insert.

 

[phlat]

Unfortunately REPLACE INTO overwrites the other two existing fields. To clearify there are four fields in the table. Two are getting populated by a previous INSERT INTO. So I need to UPDATE the remaining two somehow. This is the dilemma.

Thanks.

 

[ericbrunson]

Join the original table in the query to get the values of the other two fields in the result set, then use replace into.

 

[phlat]

I think I understand. Would you be so kind as to provide a possible example? I'm pretty new to this MySQL stuff.

Thanks again,
Chad

 

[ericbrunson]

Basically, create a select statement that produces the result set *exactly* as you want it in the table. Do you know how do join two tables in a single query?

 

[phlat]

I was able to solve my problem using a single INSERT SELECT. My problem was that I needed to cross join the other table and provide the correct WHERE conditions. I found a good tutorial that helped me with MySQL.

http://www.keithjbrown.co.uk/vworks/mysql/mysql_p5.shtml

This is my working query:

INSERT INTO
cms.project_event (projectID,eventID,projectPhaseID,sortOrder)
SELECT 1,
cms.event.eventID,
cms.projectTemplate_eventTemplate.projectTemplatePhaseID,
cms.projectTemplate_eventTemplate.sortOrder
FROM cms.event,
cms.projectTemplate_eventTemplate
WHERE
cms.event.clientID =329 AND cms.projectTemplate_eventTemplate.projectTemplateID =1 AND
cms.event.eventTemplateID = cms.projectTemplate_eventTemplate.eventTemplateID

Thanks,

Chad

 

[ericbrunson]

Good job.