How to add records based on query 2

[onyxtacular]

Hi Everyone,
I trying to add data to the table based on query of the same table. Basically we have a several thousand entries with 21 in the option filed and want to make duplicates of the entries with 50 in the option field and change the string in the description field to something else.

Although it seems like it would be a simple query I'm stuck how to write it out.

Update table cfgmaster
set option=50
where (Select *
where option=21);

I tried this but was rejected.

Thanks for viewing.

 

[SQLBill]

Your statement would only update the existing records; it wouldn't create duplicate records with the new information.

One way (and there may be easier ways):

SELECT * INTO #cfgmaster_temp
WHERE option=21

UPDATE #cfgmaster_temp
SET option = 50

INSERT INTO cfgmaster
SELECT *
FROM #cfgmaster_temp

For the INSERT INTO you may have to list out the columns, but since you didn't provide a table structure, I couldn't provide that. If you need to update another column (you said you wanted to update the description, but your example doesn't show that) just add it to the UPDATE statement.

-SQLBill




The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[bborissov]

INSERT INTO cfgmaster (Fld1, Fld2...,Options)
SELECT Fld1, Fld2...,50
FROM cfgmaster
where option=21

Borislav Borissov
VFP9 SP2, SQL Server

 

[onyxtacular]

I must be missing something...I tried running pretty much as SQLBILL gave me. Here is the actual code I used:

SELECT * INTO #cfgmaster_temp
from GNC5.dbo.Configuration_Mst
WHERE 'FEANUM_44' = '01' AND 'CFGCHR_44' = '21'

UPDATE #cfgmaster_temp
SET CFGCHR_44 = '50'

INSERT INTO Configuration_Mst
SELECT *
FROM #cfgmaster_temp

I do want to copy all fields in the record that match the where criteria.

Upon execution I get:


(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

(0 row(s) affected)

 

[bborissov]

'FEANUM_44' is NOT equal to '01' AND 'CFGCHR_44' is NOT equal to '21'.
You compare strings here, not field and value.
Use square brackets:

[FEANUM_44] = '01' AND [CFGCHR_44] = '21'

But I still prefer my way
no temp table involved in it.

Borislav Borissov
VFP9 SP2, SQL Server

 

[onyxtacular]

I'm also trying your way bborissov. It seems straight forward. I'm learning this SQL as I go.

Thanks for the help.

 

[onyxtacular]

Thanks SQLBILL and bborissov I got both of the codes working. Previously the company downloaded the data...made manual changes and then uploaded the data. The process took hours to complete, now it takes minutes. Your help is greatly appreciated.

I will do my best to pay it forward.