SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Update one row from another
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 06/22/2007 :  10:49:13  Show Profile  Reply with Quote
I need help with syntax please:

I wish to update one row from another in the same table.

declare @SourceJobNumber char(8)
declare @TargetJobNumber char(8)
select @SourceJobNumber = '07a00001'
select @TargetJobNumber = '07G50053'

UPDATE myTable
SET
field1 = @NewValue1,
field2 = @NewValue2,
field3 = @NewValue3

WHERE myTable.jobNumber = @TargetJobNumber


SELECT ???


Inserts I can do :
INSERT INTO [dbo].[job]
(
[JobNumber],
[Name],
[Date],
[Description],
[TypeID]
)
SELECT
@NewJobNumber,
[Name],
[Date],
[Description],
[TypeID]
FROM job
WHERE (JobNumber = @OriginalJobNumber)

-dw

khtan
In (Som, Ni, Yak)

Singapore
17635 Posts

Posted - 06/22/2007 :  10:53:27  Show Profile  Reply with Quote
use table alias

UPDATE t
SET    col1 = s.col1,
       col2 = s.col2
FROM   mytable s INNER JOIN mytable t



KH
Time is always against us

Go to Top of Page

rudesyle
Posting Yak Master

110 Posts

Posted - 06/22/2007 :  10:54:34  Show Profile  Reply with Quote
quote:
Originally posted by liffey

I need help with syntax please:

I wish to update one row from another in the same table.

declare @SourceJobNumber char(8)
declare @TargetJobNumber char(8)
select @SourceJobNumber = '07a00001'
select @TargetJobNumber = '07G50053'

UPDATE myTable
SET
field1 = @NewValue1,
field2 = @NewValue2,
field3 = @NewValue3

WHERE myTable.jobNumber = @TargetJobNumber


SELECT ???


Inserts I can do :
INSERT INTO [dbo].[job]
(
[JobNumber],
[Name],
[Date],
[Description],
[TypeID]
)
SELECT
@NewJobNumber,
[Name],
[Date],
[Description],
[TypeID]
FROM job
WHERE (JobNumber = @OriginalJobNumber)

-dw



Not sure what the problem is. You just want to set one column value equal to another? It looks like you're already doing that.
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 06/22/2007 :  11:04:52  Show Profile  Reply with Quote
quote:
Originally posted by khtan

use table alias

UPDATE t
SET    col1 = s.col1,
       col2 = s.col2
FROM   mytable s INNER JOIN mytable t



KH
Time is always against us





khtan

I have nothing common to join on as the job numbers will differ (source and target)

rudesyle

I wish to copy some values from one existing row to another existing row in the same table. My sample shows the jobNumbers as @Source and @Target.

My update is fine I just can't figure how to read the source row and put the data from it into the target.

Sorry but I just confused the question by including INSERT code.

-dw
Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 06/22/2007 :  11:10:20  Show Profile  Reply with Quote
Thanks guys,

I think I got it

UPDATE job
SET
field1 = s.field1
FROM job s
WHERE
(s.JobNumber = @SourceJobNumber)

-dw
Go to Top of Page

Ifor
Aged Yak Warrior

587 Posts

Posted - 06/22/2007 :  11:20:09  Show Profile  Reply with Quote
quote:
Originally posted by liffey

Thanks guys,

I think I got it

UPDATE job
SET
field1 = s.field1
FROM job s
WHERE
(s.JobNumber = @SourceJobNumber)

-dw



I think that will update all the rows in Job, not just the one you want. Try something like:

UPDATE T1
SET [Name] = T2.[Name]
    ,[Date] = T2.[Date]
    ,[Description] = T2.[Description]
    ,[TypeID] = T2.[TypeID]
FROM myTable T1
    JOIN myTable T2
        ON T2.JobNumber = @SourceJobNumber
            AND T1.JobNumber = @TargetJobNumber


Go to Top of Page

liffey
Yak Posting Veteran

Ireland
58 Posts

Posted - 06/22/2007 :  11:42:10  Show Profile  Reply with Quote
Thanks Ifor. Solution complete.

-dw
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000