Eli R Eli R - 1 year ago 71
MySQL Question

mySQL update all column fields only if all column fields are = something

Sorry if this is not clear, but ill try my best to make it as clear as possible.

so basically i want to update all column records if all column records are = something.

For Example:

Name id Col1 Col2
Row1 1 6 1
Row2 2 2 1
Row3 3 9 1
Row4 4 16 1

If all Col2 records = 1 then set them all to = 2

So the table is gonna update to this:

Name id Col1 Col2
Row1 1 6 2
Row2 2 2 2
Row3 3 9 2
Row4 4 16 2

But if the table is like this:

Name id Col1 Col2
Row1 1 6 1
Row2 2 2 1
Row3 3 9 1
Row4 4 16 2

Nothing is gonna change, since not all of the records of Col2 are = 1.

I'v got this code, which i know exactly why it does't work the way i want it, but i don't have enough knowledge to fix it:

UPDATE test as test,
SELECT test.Col2 FROM test WHERE test.Col2 = '1'
) as temp
SET test.Col2 = '2' WHERE temp.id = test.id

Thank you in advance.

Answer Source

Bit of a tricky one. Instead of using simple comparisions you will have to use counts

UPDATE test, 
(SELECT COUNT(*) as c1 FROM test where Col2=1) as a1, 
(SELECT COUNT(*) as c2 FROM test) as s2 
SET test.Col2=2 WHERE c1=c2;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download