Need help with a SQL 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)

Need help with a SQL query

Author

Topic

GeRm
Starting Member

4 Posts

Posted - 2011-05-06 : 19:58:51

Im trying to update all passengers whos birth month is in November to First Class. I used update travels, passenger set class = 'First Class' where travels.class = 'Economy' and MONTH(passenger.dob) = '11';

That updates all the records!!! Not just people born in November.

Please advise...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 20:04:22

You need something that relates the travels table with the passenger table. In other words, if you look at a row in travels table, what column tells you which passenger that belongs to? In the following I am assuming that that is a passenger_id. If so:


UPDATE t
SET    class = 'First Class'
FROM   travels t
       INNER JOIN passenger p
            ON  p.passenger_id = t.passenger_id
WHERE  MONTH(p.dob) = 11
       AND t.class = 'Economy'

GeRm
Starting Member

4 Posts

Posted - 2011-05-06 : 20:10:30

the row that shows which passenger is cust_id. but your query doesnt seem to work. what do you mean by travels t and passener p?

GeRm
Starting Member

4 Posts

Posted - 2011-05-06 : 20:21:33

any ideas??

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-06 : 20:26:23

Sorry about that - I am not very familiar with SQL 2000, I sort of assumed that it would work the same way as in SQL 2005 or SQL 2008. Can you try to see if this works:


UPDATE passenger travels 
SET    class = 'First Class'
FROM   travels 
       INNER JOIN passenger 
            ON  passenger.cust_id= travels.cust_id
WHERE  MONTH(passenger.dob) = 11
       AND travels.class = 'Economy'

or this


UPDATE passenger travels 
SET    class = 'First Class'
FROM   travels,passenger 
WHERE  passenger.cust_id= travels.cust_id
       AND MONTH(passenger.dob) = 11
       AND travels.class = 'Economy'

Edit: Corrected a silly error after jesuraja posted the corrected query.

GeRm
Starting Member

4 Posts

Posted - 2011-05-07 : 00:59:22

still not working. I really appreciate your help. It is saying the error is from "from travels, passenger to the last part

jesuraja
Starting Member

3 Posts

Posted - 2011-05-07 : 02:54:46

quote:
Originally posted by GeRm

still not working. I really appreciate your help. It is saying the error is from "from travels, passenger to the last part

Try this:

UPDATE travels
SET class = 'First Class'
FROM travels INNER JOIN
passenger ON passenger.cust_id= travels.cust_id
WHERE MONTH(passenger.dob) = 11
AND travels.class <> 'First Class'

Jesuraja I

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-07 : 07:58:13

doh!

Thanks jesuraja. I corrected my previous post.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources