quote:

---

Originally posted by sqlchiq

is it possible to do something like this....

I'm trying to update an entire section of a column that has incomplete dates. Basically every date with executiontime of 8/8/2008 has incomplete dates. I have to match it up with entries from another day to get the complete day.

basically the saildate column looks like this

Apr 23
Apr 23
Jan 1
Feb 4

But it should be
Apr 23, 2009
Apr 23, 2010
Jan 1, 2009
Feb 4, 2009


There are other entries in the same table with the correct dates, so I'm trying to update these older incomplete entries with the newer complete ones.

I've tried the following...

update carnysrock
set t1.saildate = t2.saildate
from carnysrock as t1, carnivalcleaned as t2
where t2.executiontime = '08/12/2008' and t1.interiorprice = t2.interiorprice and t1.balconyprice = t2.balconyprice and t1.oceanviewprice = t2.oceanviewprice
and left(t1.saildate, 6) = left(t2.saildate,6) and t1.shipname = t2.shipname




and




UPDATE carnysrock as t1
SET t1.saildate = (SELECT t2.saildate

FROM
carnivalcleaned as t2
WHERE t2.executiontime = '08/12/2008' and t1.interiorprice = t2.interiorprice and t1.balconyprice = t2.balconyprice and t1.oceanviewprice = t2.oceanviewprice
and left(t1.saildate, 6) = left(t2.saildate,6) and t1.shipname = t2.shipname)


I can't seem to get it to work, does anyone have any ideas??

---