Update one row from another - 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 one row from another

Author

Topic

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-06-22 : 10:49:13

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)

17689 Posts

Posted - 2007-06-22 : 10:53:27

use table alias

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

KH
[spoiler]Time is always against us[/spoiler]

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-22 : 10:54:34

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.

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-06-22 : 11:04:52

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
[spoiler]Time is always against us[/spoiler]

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

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-06-22 : 11:10:20

Thanks guys,

I think I got it

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

-dw

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-22 : 11:20:09

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

liffey
Yak Posting Veteran

58 Posts

Posted - 2007-06-22 : 11:42:10

Thanks Ifor. Solution complete.

-dw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources