Update Query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Update Query

Author

Topic

chipembele
Posting Yak Master

106 Posts

Posted - 2008-01-30 : 06:10:39

Hi
I'm totally lost on this one.

I have a table that I need to update and it goes something like this. Each year record is on a separate line.

Update Detail

SetDestination = '155'

If (Year = 06/07 and CODE LIKE FPFT1%) AND (Year = '07/08' AND CODE LIKE FPFT2%)

The problem is the two years being on separate lines. i've tried to create a view that selects the data, calls Code 0607Code AND 0708Code then tried to do it like that but am struggling.

My code looks like this at the moment and it was from this that I then try to do the update on

SELECT dbo.dbstudentdetail.RefNo, dbo.dbstudentdetail.FirstForename, dbo.dbstudentdetail.Surname, 
                      dbo.UpdateDests0607Data.Code AS [0607], dbo.UpdateDests0708Data.Code AS [0708]
FROM         dbo.dbstudentdetail LEFT OUTER JOIN
                      dbo.UpdateDests0708Data ON dbo.dbstudentdetail.RefNo = dbo.UpdateDests0708Data.RefNo LEFT OUTER JOIN
                      dbo.UpdateDests0607Data ON dbo.dbstudentdetail.RefNo = dbo.UpdateDests0607Data.RefNo
WHERE     (dbo.UpdateDests0607Data.Code LIKE 'FPFT1%') AND (dbo.UpdateDests0708Data.Code LIKE 'FPFT2%')
ORDER BY dbo.dbstudentdetail.RefNo

I'm not sure if its possible to do an update on data selected from a view.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 06:44:54

You need to post some sample data and your table structure to make this clear.

chipembele
Posting Yak Master

106 Posts

Posted - 2008-01-30 : 07:03:51

Example Data

Student Detail Table

Year    Ref No  Name        Destination
06/07   923456  Bob Smith
07/08   923456  Bob smith


Enrolment Table
Year   Ref No   CodeId
06/07  923456   96
07/08  923456   97

Code Table
CodeId    Code
96        FPFT1
97        FPFT2

In this case if in 06/07 that person was on FPFT1 and in 07/08 the person was on FPFT2 then after running the query the student detail would look like this.


Year    Ref No  Name        Destination
06/07   923456  Bob Smith      155
07/08   923456  Bob smith

These are tables in simplified form.

I hope this is easier to understand than it was for me to try to explain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 07:21:39

Try:-
Update sd
SET Destination=155
FROM StudentDetail sd
INNER JOIN Enrolment e
ON e.Year=sd.Year
AND e.Ref No =sd.Ref No
INNER JOIN CodeTable ct
ON ct.CodeId=e.CodeId
WHERE ((e.Year='06/07' AND ct.Code='FPFT1')
OR (e.Year='07/08' AND ct.Code='FPFT2'))

chipembele
Posting Yak Master

106 Posts

Posted - 2008-01-30 : 08:12:59

Thanks for this

I just noticed a mistake I made in my example. There is no Year in the enrolment table, its in the Code table and the Student Detail table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-30 : 08:39:22

quote:
Originally posted by chipembele

Thanks for this

I just noticed a mistake I made in my example. There is no Year in the enrolment table, its in the Code table and the Student Detail table.

then do like this:-

Update sd
SET sd.Destination=155
FROM StudentDetail sd
INNER JOIN Enrolment e
AND e.Ref No =sd.Ref No 
INNER JOIN CodeTable ct
ON ct.CodeId=e.CodeId
AND ct.Year=sd.Year
WHERE ((sd.Year='06/07' AND ct.Code='FPFT1')
OR (sd.Year='07/08' AND ct.Code='FPFT2'))

chipembele
Posting Yak Master

106 Posts

Posted - 2008-01-31 : 03:21:41

Thanks visakh16

I've run it on my database after making sure all the tables are named correctly and that i've identified the correct keys but it doesnt like the 'AND'

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AND'.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-01-31 : 03:29:03

if your column name is really Ref no (i.e. with a space), you'll need to write [ref no]

Em

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-31 : 03:29:17

quote:
Originally posted by chipembele

Thanks visakh16

I've run it on my database after making sure all the tables are named correctly and that i've identified the correct keys but it doesnt like the 'AND'

Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'AND'.

Got it change like this

Update sd
SET sd.Destination=155
FROM StudentDetail sd
INNER JOIN Enrolment e
ON e.Ref No =sd.Ref No 
INNER JOIN CodeTable ct
ON ct.CodeId=e.CodeId
AND ct.Year=sd.Year
WHERE ((sd.Year='06/07' AND ct.Code='FPFT1')
OR (sd.Year='07/08' AND ct.Code='FPFT2'))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources