By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,610 Members | 2,310 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,610 IT Pros & Developers. It's quick & easy.

Sql help

P: n/a
vp
Hi All

I have a table with this layout (example 2 rows with 4 columns)

PK1 STATE1 PROV1 PLAN1
pk2 state2 prov2 plan2

If STATE1 and PROV1 equals state2 and prov2 than I have to plug in pk2
to a field.

How can I do it in SQL?? Please help need

thanks,

Mar 21 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
I am not sure i understand it all, but perhaps this is what you want:

SELECT col1, col2, MAX(col3) FROM table GROUP BY col1, col2 HAVING
COUNT(*) > 1

B.

Mar 21 '06 #2

P: n/a
vp
PK1 STATE1 PROV1 PLAN1
pk2 state2 prov2 plan2
If (STATE1=state2 and PROV1=prov2 ) than take pk2 to plug in to
another field
Should I do a self join on the table to check this condition??? or
please advice

thanks,

Mar 21 '06 #3

P: n/a
vp wrote:
PK1 STATE1 PROV1 PLAN1
pk2 state2 prov2 plan2
If (STATE1=state2 and PROV1=prov2 ) than take pk2 to plug in to
another field
Should I do a self join on the table to check this condition??? or
please advice

thanks,

As Joe Celko often says, some DDL would help, but here's a guess based
on my best guess based on what little data I have.

Perhaps you want something like:

UPDATE some_other_table SET some_column=(
SELECT pk FROM first_table
WHERE state='state2'
AND province='prov2')
Mar 21 '06 #4

P: n/a
vp
PK1 spk1 STATE1 PROV1 PLAN1
pk2 spk2 state2 prov2 plan2

Tried the query for the able table( using a self join)

SELECT DISTINCT
case when a.PROV1 IS NOT NULL and
(a.PROV1=b.plan2 and a.STATE1=b.state2 )
then b.pk2
else a.prov1 end,
FROM PROVIDER a, PROVIDER b
WHERE (a.PK1=b.PK2 and a.spk2=b.spk2)
Please let me know if this is correct or suggest some thing...

thanks

Mar 23 '06 #5

P: n/a
vp
PK1 spk1 STATE1 PROV1 PLAN1
pk2 spk2 state2 prov2 plan2

Tried the query for the able table( using a self join)
SELECT DISTINCT
case when a.PROV1 IS NOT NULL and
(a.PROV1=b.plan2 and a.STATE1=b.state2 )
then b.pk2
else a.prov1 end,
FROM PROVIDER a, PROVIDER b
WHERE (a.PK1=b.PK2 and a.spk2=b.spk2)
Please let me know if this is correct or suggest some thing...

Mar 23 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.