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.
| Author |
Topic |
|
aax
Starting Member
11 Posts |
 Posted - 2005-08-25 : 14:35:32
|
| How would I do an update query to update the linerText column value with Null values from the select statement below? I know how to create an update statement but don't know how to add it or make it work with the select statement below.update lnr_ad_detail set linerText = '' where VALUES FROM SELECT BELOW------------------select top 200 b.vehicle_id, (select count(*) from lnr_ad_detail tor inner join lnr_ad_header bor on tor.ad_header_id = bor.ad_header_id where bor.created_on between '1/25/2004' and '8/25/2005' and tor.vehicle_id = b.vehicle_id and bor.company_id = '1' and tor.status_code = 1) as car_exists, b.vin, b.stock_number, vm.year, vm.make, vm.model, vm.series, b.mileage, dd.creation_date as creation_date, d.sent_date as liner_ad_sent_date, isnull(b.in_value, 0) as in_value, b.recon, b.condition_id, b.acv, isnull(b.retail_value, 0) as retail_value, days_inv = case when b.status_id = 13 then dateDiff(dd,in_date, getdate())end, cast(b.vehicle_id as varchar(20)) + ',' + cast(isnull((select top 1 vehicle_photo_id from aax_vehicle_photos where vehicle_id = b.vehicle_id and prefered = 1),-1) as varchar(20)) as liner_ad_vpic_id, cast((select count(*) from aax_vehicle_photos where vehicle_id = b.vehicle_id) as varchar(20)) + ' - ' + case cast((select count(*) from aax_vehicle_photos where vehicle_id = b.vehicle_id and prefered = 1) as varchar(15)) when '0' then 'N' else 'Y' end as pinfo, ' + @varColumns + ' as linerText, (select count(0) from lnr_ad_detail h where h.vehicle_id = b.vehicle_id and h.status_code = 1) as pend, (select top 0 ud.original_text from lnr_ad_detail ud inner join lnr_ad_header uh on uh.ad_header_id = ud.ad_header_id where ud.vehicle_id = b.vehicle_id and uh.created_on = dd.creation_date and uh.company_id = '1') as original_text from StickEmUp.dbo.vehicle a with (nolock) right outer join aax_vehicle b with (nolock) on a.vin = b.vin and a.company_id = b.company_id inner join aax_vin_master vm with (nolock) on b.vin = vm.vin left outer join aax_condition_ctl acc on b.condition_id = acc.condition_id left outer join (select max(f.date_sent) as sent_date, k.company_id, g.vehicle_id from lnr_ad_header f inner join lnr_ad_detail g on f.ad_header_id = g.ad_header_id inner join aax_vehicle k on g.vehicle_id = k.vehicle_id group by k.company_id, g.vehicle_id) d on b.vehicle_id=d.vehicle_id left join (select max(ff.created_on) as creation_date, kk.company_id, gg.vehicle_id from lnr_ad_header ff inner join lnr_ad_detail gg on ff.ad_header_id = gg.ad_header_id inner join aax_vehicle kk on gg.vehicle_id = kk.vehicle_id where ff.created_on between '1/25/2004' and '8/25/2005' group by kk.company_id, gg.vehicle_id) dd on b.vehicle_id=dd.vehicle_id left join lnr_ad_detail e on b.vehicle_id=e.vehicle_id left join lnr_ad_header f on e.ad_header_id = f.ad_header_id where f.created_on between '1/25/2004' and '1/25/2005' and b.company_id = '1' |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-08-25 : 14:40:06
|
| Did you write that, or did someone give it to you?First thing to is lose NOLOCKSecond, what excatly are you trying to do?Now I gotta take some aspirinBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
 |
|
|
aax
Starting Member
11 Posts |
Posted - 2005-08-25 : 15:12:21
|
| Someone else wrote this and I am trying to modify it. Actually I just want to update records in the database that meet this criteria and want to add the update to it. Is it possible and easy enough? |
|
|
|
|
|
|
|
|
Update values from a select statement