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

General SQL Server Forums

New to SQL Server Programming

SQL Query

Author

Topic

chris2332
Starting Member

3 Posts

Posted - 2010-06-21 : 07:18:32

Hi all,

This is my first post and I wanna say hi to all and that this is a great very helpful forum.

Now, I have a query that I am not quite sure how to create:

I have the following two tables:

Table A:
A_ID
A_FLAG

Table B:
B_ID
B_FLAG

I am trying to do the following:

Using A_ID (to link to B_ID), retrieve all B_FLAGs.

Then, if no B_FLAG is set to 'Y', set A_FLAG ) to 'N'
If one or more B_FLAGs is set to 'Y', set A_FLAG to 'Y'

I want to set A_FLAG for all records returned - some records in table A might no be linked to table B. I only want to set the A_FLAG for the records that are linked to table B

I am finding really hard to formulate the query.

Any help will be much appreciated.

Thanks,
Chris.

Ifor
Aged Yak Warrior

700 Posts

Posted - 2010-06-21 : 08:02:23

[code]
UPDATE A
SET A_FLAG =
CASE
WHEN D.B_FLAG = 'Y'
THEN 'Y'
ELSE 'N'
END
FROM TableA A
JOIN
(
SELECT B.B_ID, MAX(B.B_FLAG) AS B_FLAG
FROM TableB B
GROUP BY B.B_ID
) D
ON A.A_ID = D.B_ID
[/code]

chris2332
Starting Member

3 Posts

Posted - 2010-06-21 : 08:52:59

Thank you very much for your reply Ifor.

I will try and run it and see how it works.

chris2332
Starting Member

3 Posts

Posted - 2010-06-21 : 09:02:13

Works like a charm....

Really much appreciated your fast reply Ifor :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources