Duplicate rows with a new category id? Try this:
INSERT INTO orders (post_key, cat_id, order_type, group_name)
SELECT post_key, 7, order_type, group_name
FROM orders
WHERE cat_id = 6;
In my experience, using an INSERT…SELECT approach works effectively while ensuring that modified fields are explicitly defined in the SELECT part of the query. I have also found it beneficial to first validate the results of the SELECT query to ensure that only the correct records are being manipulated. The process can be safeguarded by using transactions, especially when dealing with live data, to prevent any unintended changes. This method has proven reliable and clear for duplicating records with specific modifications over long-term projects.
hey, i also tried copying rows into a tmp table first then doing an update. it lets you check changes before commit, kinda giving you a rollback chance if something goes wonky. works well if your system supports it.
hey, ever used a staging tbl to double-check changes before final insertion? i tried that once and it helped catch silly issues. how do u guys feel about its efficiency compared to cte? would love to get your experince and ideas.
hey, try a cte for clarity. ex: with rows as (select * from orders where cat_id=6) insert into orders (post_key,cat_id,order_type,group_name) select post_key,7,order_type,group_name from rows; works gr8 for me
hey, i wonder if u tried a derived table method? i used one time a union query to duplicate modified rows and it was neat but sometimes quirky. what regar u technique though, any got insights?