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
 General SQL Server Forums
 New to SQL Server Programming
 Update from Select Distinct Statement..
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dodyryda1
Starting Member

12 Posts

Posted - 12/16/2011 :  12:25:48  Show Profile  Reply with Quote
Hi..

I'm pretty new to this so please bear with me
I've written my insert statement as below.. which works a treat but unlike the insert statement it seems one can not use update in the same manner ie Update(field1,field2,field3). Can anyone help how I can convert the following statement into an update?
I already have my test for the update established.. It would be really easy if the update worked the same way as the insert..


INSERT INTO JobSpec(ID, Branch, [Job Type], [Company Name], [Site Location],[Contract No], Equipment)
SELECT DISTINCT
CAL.ID, DEP.Name As Depot, 'DELIVER' As [Job Type], TBL1.Acctname As [Company Name],
CASE
WHEN TBL1.DELADDR#3 = '' and TBL1.DELADDR#2 = '' and TBL1.DELADDR#1 = '' and TBL1.DELNAME = '' then 'CUST COLLECT'
WHEN TBL1.DELADDR#3 = '' and TBL1.DELADDR#2 = '' and TBL1.DELADDR#1 = '' THEN TBL1.DELNAME
WHEN TBL1.DELADDR#3 = '' and TBL1.DELADDR#2 = '' THEN TBL1.DELADDR#1
WHEN TBL1.DELADDR#3 = '' and TBL1.DELADDR#2 <> '' then TBL1.DELADDR#2
WHEN TBL1.DELADDR#3 <> '' THEN TBL1.DELADDR#3
End As [Site Location], TBL1.ContNo As [Contract No],
OnSameLine = substring( ( SELECT ', ' + coalesce (CAST(CNT.qty AS varchar(1000)), '')+ ' x ' + '['+ coalesce (CNT.Itemno,'') + ']' + ' ' + coalesce (STK.Desc#1,'')
FROM [SERVER].db.dbo.Contitems CNT
INNER JOIN [SERVER].db.dbo.Contracts TBL on CNT.ContNO = TBL.ContNo
INNER JOIN [SERVER].db.dbo.stock STK on CNT.ItemNo = STK.ItemNo
WHERE (CNT.ContNO = cnt1.Contno) and cnt.deldate = cnt1.deldate FOR XML path(''), elements
),2,500)
FROM [SERVER].db.dbo.Contitems CNT1
INNER JOIN [SERVER].db.dbo.Contracts TBL1 on CNT1.ContNO = TBL1.ContNo
Inner join [SERVER].db.dbo.Depots DEP on TBL1.Hiredepot = DEP.Code
FULL OUTER JOIN dbo.calendar CAL on (cal.[Job Date] = CNT1.DelDate)
FULL OUTER JOIN dbo.JobSpec JB on (jb.PK = cal.ID)
WHERE TBL1.source = '0' and (CNT1.Status = 0) and TBL1.status <> 9 and (TBL1.DelDate>=@OneWeekAgo)

X002548
Not Just a Number

15586 Posts

Posted - 12/16/2011 :  12:39:34  Show Profile  Reply with Quote
Well what table do you want to Update?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx


Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/


Go to Top of Page

dodyryda1
Starting Member

12 Posts

Posted - 12/16/2011 :  13:01:15  Show Profile  Reply with Quote
Hi Brett..

my jJobSpec table..

I had something like below but unsure what to do next


UPDATE JobSpec
Set ID = CAL.ID, Branch = DEP.Name, [Job Type] = 'DELIVER', [Company Name] = TBL.Acctname,
[Site Location] = CASE
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' and TBL.DELADDR#1 = '' and TBL.DELNAME = '' then 'CUST COLLECT'
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' and TBL.DELADDR#1 = '' THEN TBL.DELNAME
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' THEN TBL.DELADDR#1
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 <> '' then TBL.DELADDR#2
WHEN TBL.DELADDR#3 <> '' THEN TBL.DELADDR#3
End, Equipment = dbo.ufnItemsOnSameLine(TBL.ContNo,', '), [Contract No] = TBL.ContNo
from JobSpec
Go to Top of Page

dodyryda1
Starting Member

12 Posts

Posted - 12/16/2011 :  13:04:36  Show Profile  Reply with Quote
actually that was a bit of an old attempt but the jobspec fields are correct..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/16/2011 :  13:19:50  Show Profile  Reply with Quote

UPDATE JB
Set ID = CAL.ID, Branch = DEP.Name, [Job Type] = 'DELIVER', [Company Name] = TBL.Acctname,
[Site Location] = CASE 
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' and TBL.DELADDR#1 = '' and TBL.DELNAME = '' then 'CUST COLLECT'
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' and TBL.DELADDR#1 = '' THEN TBL.DELNAME
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 = '' THEN TBL.DELADDR#1
WHEN TBL.DELADDR#3 = '' and TBL.DELADDR#2 <> '' then TBL.DELADDR#2
WHEN TBL.DELADDR#3 <> '' THEN TBL.DELADDR#3
End, Equipment = dbo.ufnItemsOnSameLine(TBL.ContNo,', '), [Contract No] = TBL.ContNo
FROM dbo.JobSpec JB
INNER JOIN dbo.calendar CAL on (jb.PK = cal.ID)
INNER JOIN [SERVER].db.dbo.Contitems CNT1 on (cal.[Job Date] = CNT1.DelDate)
INNER JOIN [SERVER].db.dbo.Contracts TBL on CNT1.ContNO = TBL.ContNo
Inner join [SERVER].db.dbo.Depots DEP on TBL.Hiredepot = DEP.Code
WHERE TBL1.source = '0' and (CNT1.Status = 0) and TBL1.status <> 9 and (TBL1.DelDate>=@OneWeekAgo)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dodyryda1
Starting Member

12 Posts

Posted - 12/16/2011 :  13:56:12  Show Profile  Reply with Quote
Thanks tried it but this doesn't work. No longer using the user defined function
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/17/2011 :  01:44:29  Show Profile  Reply with Quote
quote:
Originally posted by dodyryda1

Thanks tried it but this doesn't work. No longer using the user defined function


why it didnt work?
what error you got?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dodyryda1
Starting Member

12 Posts

Posted - 12/19/2011 :  05:27:29  Show Profile  Reply with Quote
sorry for the late reply.. it wont work because I'm concentrating several rows from the contitems table..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/19/2011 :  06:09:42  Show Profile  Reply with Quote
hmm...so? i didnt get the point

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

dodyryda1
Starting Member

12 Posts

Posted - 12/20/2011 :  14:34:58  Show Profile  Reply with Quote
I had to tackle this a different way in the end. Partly because both the date and the data could be altered. So could either be more or less rows than the original entry. I decided to delete then re insert the new data. There maybe a better method with merge on SQL 2008 but I'm only using 2005.

On my phone at the mo but will try and post the code tomorrow.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 12/21/2011 :  12:17:55  Show Profile  Reply with Quote
I still didnt understand your scenario
Anyways if you've sorted it out thats good news...

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.12 seconds. Powered By: Snitz Forums 2000