Update query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-26 : 08:47:29

Can I do an update query like

Update Table1
set table1.*= Table2.*
where Table2.id = table1.id

This would be nice as I have rather a few columns and don't really want to have to write it all out.

Hope that makes sense

ICW

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-02-26 : 08:50:16

No you will have to explicitly mention all the columns you want to update.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-02-26 : 08:52:20

Run this and from the result add required conditions

select
'table1.'+column_name+'= Table2.'+column_name+','
from information_schema.columns
where table_name='table1'

Madhivanan

Failing to plan is Planning to fail

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-26 : 08:58:29

I just tried the query below and it said "the multi-part identifier "TropicsBHeader.BookingNo" could not be bound"

WHat does that mean?

UPDATE [Milestones].[dbo].[MSTROPICSBHeader]
   SET [RecType] = [TropicsBHeader].RecType 
      ,[SellingCompany] = [TropicsBHeader].SellingCompany
      ,[BookingNo] = [TropicsBHeader].BookingNo
      ,[Status] = [TropicsBHeader].Status 
      ,[BookingDate] = [TropicsBHeader].BookingDate
      ,[LastChanges] = [TropicsBHeader].LastChanges
      ,[AgentCode] = [TropicsBHeader].AgentCode
      ,[Consultant] = [TropicsBHeader].Consultant
      ,[ConsortCode] = [TropicsBHeader].ConsortCode
      ,[Currency] = [TropicsBHeader].Currency
      ,[TotalPrice] = [TropicsBHeader].TotalPrice 
      ,[TotalPaid] = [TropicsBHeader].TotalPaid
      ,[TotalCommission] = [TropicsBHeader].TotalCommission
      ,[TotalTax] = [TropicsBHeader].TotalTax 
      ,[RelatedBooking] = [TropicsBHeader].RelatedBooking 
      ,[GrpFlag] = [TropicsBHeader].GrpFlag
      ,[FirstDepDate] = [TropicsBHeader].FirstDepDate 
      ,[LastDepdate] = [TropicsBHeader].LastDepdate
      ,[PayStatus] = [TropicsBHeader].PayStatus
      ,[BookingChannel] = [TropicsBHeader].BookingChannel
 WHERE 
		[TropicsBHeader].BookingNo = [MSTROPICSBHeader].BookingNo

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-26 : 09:01:50

sorry i figured it out

UPDATE [Milestones].[dbo].[MSTROPICSBHeader] 
   SET [RecType] = [TropicsBHeader].RecType 
      ,[SellingCompany] = [TropicsBHeader].SellingCompany
      ,[BookingNo] = [TropicsBHeader].BookingNo
      ,[Status] = [TropicsBHeader].Status 
      ,[BookingDate] = [TropicsBHeader].BookingDate
      ,[LastChanges] = [TropicsBHeader].LastChanges
      ,[AgentCode] = [TropicsBHeader].AgentCode
      ,[Consultant] = [TropicsBHeader].Consultant
      ,[ConsortCode] = [TropicsBHeader].ConsortCode
      ,[Currency] = [TropicsBHeader].Currency
      ,[TotalPrice] = [TropicsBHeader].TotalPrice 
      ,[TotalPaid] = [TropicsBHeader].TotalPaid
      ,[TotalCommission] = [TropicsBHeader].TotalCommission
      ,[TotalTax] = [TropicsBHeader].TotalTax 
      ,[RelatedBooking] = [TropicsBHeader].RelatedBooking 
      ,[GrpFlag] = [TropicsBHeader].GrpFlag
      ,[FirstDepDate] = [TropicsBHeader].FirstDepDate 
      ,[LastDepdate] = [TropicsBHeader].LastDepdate
      ,[PayStatus] = [TropicsBHeader].PayStatus
      ,[BookingChannel] = [TropicsBHeader].BookingChannel
FROM [TropicsBHeader]
 WHERE 
		[TropicsBHeader].BookingNo = [MSTROPICSBHeader].BookingNo

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-26 : 10:07:11

Please,I really need someone to tell me how to change an import SSIS package to be an update package.
Which bit do i edit within the dataflow task, is it the source, the destination? All i want to do is to automatically update a table with the latest changes from head office.

It's driving me NUTS!!! usually I can click around and find stuff but this has me baffled!

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2008-02-26 : 10:50:41

If anyone else is struggling with this. Its actually far simpler than I thought.
You don't need any dataflow tasks and you only need one connection.
All I did was to put a "Execute a SQL task" on the control flow. DOuble click on it and specify the connection and write the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources