update all fields in a table
-
I have a table that needs to get updated based on an excel file that is going to be given to me. For example, the fields would be: ID (Primary Key), price, and description. Several prices and descriptions need to be updated and they are doing so in an excel file that is going to be sent to me so I can update the appropriate IDs. I was going to import the excel file into another table and then run an update statement to update the price and description (and all other fields) by joining on the IDs from the new table and the table that needs updated. How could I create an update statement that would update ALL fields in a table without doing
update Table1
Set Price = p.price, desc = p.desc, etc.........from temp1 p where table1.id = p.id
Thanks!
-
Well if you're that lazy, and you know that all of the data is in the new( excell ) table (no columns missing), you could join the two table to perform a delete in the destination table and then an insert into/select from ...
Of course wrapping both with a single transaction...
But do yourself a favor and start using an editor that allows column cut/paste and customizeable macros. That way, you can be more correct, it'll be easier to code, and your code will be more readable.
-
Am assuming that the column names will be the same in both the new and old tables. You can use the below query then:
Declare @updateSQL varchar(1000)
Set @updateSQL = 'Update Table1 Set '
Select @updateSQL = @updateSQL + '['+column_name+'] = p.[' +column_name+'],' From information_schema.columns
where [table_name]='Temp' order by ordinal_position
Set @updateSQL = LEFT(@updateSQL,len(@updateSQL)-1)
Set @updateSQL = ' From Temp P,Table1 Where table1.id=P.id'
sp_ExecuteSQL @updateSQL
-
Why do you guys keep posting dynamic sql solution when he clearly doesn't need one????????????
If he's too lazy to write a 3 column update, and by the way the same he posted here, then he should definitly start seeking a new career.
-
I'm sorry, it's not a three column update, it's at least a 30 column update and I already found a separate solution that would cover this, sometimes I just like to get others opinions so I can increase my SQL skills. Extremely offended by your posting.
-
Still doesn't require dynamic sql. I'm not mad at you but at everybody who suggest these solution. Still 30 columns is not that long to write since you can use QA to generate the code for you. Sorry if I offended you.
-
Well, if anything, I appreciate everyone's replies because it always gives me insight on other issues and is a good way to enhance my SQL knowledge - which I think is one of the main reasons this forum exists.
-
JMeyer32,
You should not feel offended. Remi was really trying to disuade others from pointing you in the wrong direction. I know that sometimes it does not come accross as you expect but I sure that was not the intention. By the way I think 30 columns are not that many. I have worked with 300-400
My advice to you is to try to get out of the tools as much as you can!
For example:
- knowing that dragging the Columns folder on QA from under a table into the editor gives you a column list
- That Right clicking a table can generate CRUD statements for you ... etc
But Never ever sacrifice quality for typing length
* Noel -
JUser_DBA (4/26/2005)
I'm sorry, it's not a three column update, it's at least a 30 column update and I already found a separate solution that would cover this, sometimes I just like to get others opinions so I can increase my SQL skills. Extremely offended by your posting.
I'm with you Journeyman. I have a 75 columns table and I would rather write a 4-5 lines query then a monstrous one. Plus that if the table structure changes I would rather not go through all my queries to add the new column...
The answer from above, actually helped me...
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply