Print UPDATE statement for a row based on select results?

Question

I have a scenario where there is Prod/Test and in the Test environment, I have a settings table (row) with many columns that I'd like to be able to run an UPDATE after a database refresh and set everything back.

This select would return a row of columns I want to preserve.

select * from mysettings a where a.company = 'abc'

Is there a way to dynamically take this result and output something like this:

UPDATE mysettings
    set field1 = 'some str',
        field2 = 2,
        field3 = 234234,
        field4 = '2016-08-23 18:51:42.000',
        ...
    where mysettings.company = 'abc'

The reason I say Dynamically is I have different companies and environments, and also some fields are strings, int, datetime, etc and the goal is to save this script off as a final "step" to perform.

EDIT: Similar to this method, except instead of INSERT I want UPDATE.

How to export all data from table to an insertable sql format?

Are you asking can we convert results of select into an update? — TheGameiswar, Aug 23 '16 at 19:13
Basically. So that I can update the same row with the original data. I am wanting to save it off to a *.sql file to run later. So not update from a select, because it's the same row and same table. — William YK, Aug 23 '16 at 19:14
Does this help? [Link](http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match) — Sumeet Gupta, Aug 23 '16 at 19:51

score 0 · Answer 1 · answered Aug 23 '16 at 21:19

0

Would something like this work for you?

SELECT 'UPDATE mysettings 
SET field1 = ''' + mysettings.field1 + ''',
SET field2 = ''' + mysettings.field2 + ''',
...
WHERE mysettings.company = ''' + @company_name + ''''
FROM mysettings WHERE mysettings.compnay = @company_name

answered Aug 23 '16 at 21:19

Robert Sievers

1,277
10
15

I think so, but I'd need the `SET` lines dynamically created – William YK Aug 23 '16 at 21:26

score 0 · Answer 2 · edited Aug 25 '23 at 02:49

0

Try this code it will work

UPDATE mysettings
    set field1 = 'some str',
        field2 = 2,
        field3 = 234234,
        field4 = '2016-08-23 18:51:42.000',
        ...
    where mysettings.company = 'abc'

print   ''select  * from  mysettings where  where mysettings.company ='abc'+''

edited Aug 25 '23 at 02:49

Jeremy Caney

7,102
69
48
77

answered Aug 23 '23 at 10:37

Anandu

1
2

https://i.stack.imgur.com/zVlJq.png Click to Show result – Anandu Aug 23 '23 at 12:46
Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. **Would you kindly [edit] your answer to include additional details for the benefit of the community?** – Jeremy Caney Aug 25 '23 at 02:49

score 0 · Answer 3 · answered Aug 23 '23 at 12:45

[BEGIN TRANSACTION Update_dQuery
 
  UPDATE OM_CUSTOMER 
SET    FIRST_NAME = 'Lucky1', 
        MIDDLE_NAME = 'Luke1' 
WHERE  CUSTOMER_ID in('15042112360016','15042112360126');

 print   ''select  * from  OM_CUSTOMER where CUSTOMER_ID in('15042112360016','15042112360126')
  
   rollback TRANSACTION][1]

score 0 · Answer 4 · answered Aug 25 '23 at 03:49

Try this code it will work  
    
    //initaially Update the procedure 
    //Update Start 
UPDATE mysettings
    set field1 = 'some str',
        field2 = 2,
        field3 = 234234,
        field4 = '2016-08-23 18:51:42.000',
        ...
    where mysettings.company = 'abc


        //Update End
        
        //here is your Printing result
        //After Each line update you can get print 

   

     print   ''select  * from  mysettings where  where mysettings.company ='abc'+''

Print UPDATE statement for a row based on select results?

4 Answers4