Sql help

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

Brian Tkatch

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

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

Bob Stearns

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

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

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