SQLTeam.com | Weblogs | Forums

Query to update a first Table and to create a new entry in a second Table

Hello,

First of all, and unfortunately, I am not a developer.
Therefore my knowledge is very limited...

I would like to build an SQL UPDATE query to amend two Tables :

First Table : orders
Field : id_order
Field : current_state

I would like to update the value of the current_state Field to 99, for a list of id_order
The list of id_order could include tens of value that I get from other source
A list example could be : 201,202,203,204,205,206,207,208,209

Second Table : order_history
Field : id_order_history
Field : id_employee
Field : id_order
Field : id_order_state
Field : date_add

I would like to create a new entry for each value of the list of id_order, by incrementing by one, the id_order_history field
For all these new entries :
id_employee, would be always the same, value = 0
id_order_state, would be always the same, value = 99
date_add would be the current date and time

Would anyone know how to make this query ?

I thank you in advance for any reply.

Patrick

hi

Merge Statement . comes to mind ... Off of the top of my head !!!

Merge Statement .. how it works

Destination Table
Source Table

Where source table rows match destination table rows
update destination values with source values

Where source table rows do not match destination table rows
Insert Source Rows into Destination

image