Update from Select Distinct Statement..

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

General SQL Server Forums

New to SQL Server Programming

Update from Select Distinct Statement..

Author

Topic

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-16 : 12:25:48

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 - 2011-12-16 : 12:39:34

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/

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-16 : 13:01:15

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

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-16 : 13:04:36

actually that was a bit of an old attempt but the jobspec fields are correct..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-16 : 13:19:50

[code]
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)
[/code]

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

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-16 : 13:56:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-17 : 01:44:29

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/

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-19 : 05:27:29

sorry for the late reply.. it wont work because I'm concentrating several rows from the contitems table..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-19 : 06:09:42

hmm...so? i didnt get the point

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

dodyryda1
Starting Member

12 Posts

Posted - 2011-12-20 : 14:34:58

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-21 : 12:17:55

I still didnt understand your scenario
Anyways if you've sorted it out thats good news...

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources