wierd query

[AV1611]

table1

F1    |F2

1      |Y

2      |Y

2      |N

1      |N

2      |Y

2      |n

3      |N

1      |N

 

I need to make a query that set's all the F2's to Y where F1 is Y for each F1

 

update table1 set F2 = "Y" where F1 in (Select F1 from table1 where F2 = "Y" and F1 is the same as the parent query...)

 

Dunno how to do it, if it's even possible...

 

 

 

 

 

[trq]

I need to make a query that set's all the F2's to Y where F1 is Y for each F1[.quote]

 

The way I read that statement a simple....

 

UPDATE tbl SET F2 = 'Y' WHERE F1 = 'Y';

 

would be sufficient. What exactly do you mean by for each F1?

[AV1611]

Thorpe, that's not it.

 

I have a table with a bunch of company numbers and mfg part numbers and a field that is either a Y or a N depending on if the part is "lead free".  I need to look at each COMPANY PART NO/MFG PART NO PAIR and match it to any other COMPANY PART NO that happens to list the same MFG PART NO and if that MFG PART NO is a Y in ANY INSTANCE, then set EVERY INSTANCE of the MFG PART NO to a Y.

 

Say that three times fast...

[Barand]

[pre]

table1

F1    |F2

1      |Y      <-- are saying that because this is Y for FI value 1

2      |Y

2      |N

1      |N      <-- then this should be Y also

2      |Y

2      |n

3      |N

1      |N      <-- and this one too 

 

If so, and same goes the F1=2 as that second record has F2=Y, then shouldn't the table just be

 

F1  F2

1    Y

2    Y

3    N

[/pre]

[AV1611]

actually my table example sux and doesn't reflect reality sorry about that

 

CPN    |    MPN    |  LEADFREE

111    |    123    |  Y

222    |    224    |  Y

333    |    723    |  Y

444    |    123    |  Y

555    |    123    |  Y

666    |    224    |  N

777    |    123    |  N

888    |    123    |  N

999    |    723    |  N

 

when the query is done CPN 777 & 888 should be Y because CPN 111 is Y

also CPN 666 should be Y because CPN 222 is Y

but CPN 333 shoud remain N because not other MFG both matches and is Y

 

Better?

 

Sorry about the confusion...

[Barand]

As I implied before, your data isn't correctly normalized

 

[pre]

table1

CPN    |    MPN    |  LEADFREE

111    |    123    |  Y

222    |    224    |  Y

333    |    723    |  Y

444    |    123    |  Y

555    |    123    |  Y

666    |    224    |  N

777    |    123    |  N

888    |    123    |  N

999    |    723    |  N

 

NORMALIZED

 

CPN    |    MPN                      MPN    LEADFREE

111    |    123                      123        Y

222    |    224                      224        Y

333    |    723                      723        N

444    |    123       

555    |    123       

666    |    224       

777    |    123       

888    |    123       

999    |    723       

 

[/pre]

 

[AV1611]

Yes, I understand that Barand, but this was the data I inherited from the Paradox 4.5 DOS that you helped me with many times a couple of years ago, and I can't do anything about the table structure

 

Is there no way to do what I'm trying to do just in SQL without building a PHP script and looping through?

 

Thanks

[Barand]

Not tested, but try

 

UPDATE table1 t1  SET leadfree = 'Y'

WHERE EXISTS (SELECT * FROM table1 t2 WHERE t2.mpn = t1.mpn AND t2.leadfree='Y')

[AV1611]

That is exactly what I needed... I never thought to alias the table names to bridge the parent query to the subquery... which was my whole problem...

 

I'll let you know the final answer in case anyone cares about the syntax...

 

Thanks again.

[Barand]

or

 

UPDATE table1 SET leadfree = 'Y'

WHERE mpn IN (SELECT mpn FROM table1 WHERE leadfree = 'Y')

[AV1611]

last query you wrote gives this:

 

update lbryperm set `MFG1_ROHS` = 'Y' where `MFG1_PN` in (SELECT `MFG1_PN` FROM lbryperm WHERE `MFG1_ROHS`='Y' )

 

You can't specify target table 'lbryperm' for update in FROM clause