February 26, 2003 at 12:26 pm
Update table1 a
set a.field1 = (select b.field2 from table1 b)
where a.field3 = b.field3
I'm trying to update a table field with values from a different field in the same table.
I want to join the two tables by their primary key so that I can update one field with the other fields value for the same record.
The above statement (or something very similar) works in oracle, but I think in Transact you cannot alias a table name in an update statement (?)
Please help, thanks! :O)
February 26, 2003 at 12:35 pm
Try something like this:
update a
set a.col1 = b.col1
from table1 a inner join table1 b on a.pkey = b.pkey
where somecol=someval
Andy
February 26, 2003 at 12:49 pm
Thanks Andy. I feel a little stupid, I should have checked the books online first, it's right there as an example! But thank you!, I totally understand how it's down now. Just a slightly different syntax ;o)
February 26, 2003 at 1:53 pm
February 26, 2003 at 5:05 pm
I'm probably just missing the glaringly obvious but is this equivalent?
UPDATE Table1
Set field1 = field2
go
ILIT
Steve Hendricks
MCSD, MCDBA
AFS Consulting Group
(949) 588-9800 x15
Steve Hendricks
MCSD, MCDBA
Data Matrix
shendricks@afsconsulting.com
(949) 588-9800 x15
February 27, 2003 at 8:05 am
Now Im the one feeling dumb! When I read it originally I thought it was two tables, actually had to edit my reply to show the self join, didnt stop to rethink. A straight update should work.
Andy
March 3, 2003 at 7:47 am
Doesn't this seem like the perfect situation to build a trigger?
A.S.
MCSE, ASE, DBA
Webmaster
Applications Developer
March 3, 2003 at 12:08 pm
You could, but should you? If your goal is to have two columns always contain the same value, couldn't you just go with one column?! Humor aside, you could certainly do the update in a trigger if you needed (or wanted) the data to be updated real time rather than relying on a job/batch to do it.
Andy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply