[SOLVED] Sql WHERE over multiple fields?

[Mutley]

Is it possible to do a WHERE statement over several fields, so...

 

field1 = 3

field2 = 4

field6 = 9

field0 = 1

 

Then...

$sql = "UPDATE table SET "the field" = '00' WHERE "the fields" = '4';

 

Result...

 

field1 = 3

field2 = 00

field6 = 9

field0 = 1

 

Thanks.

[nicangeli]

N00b Here,

 

Why not just runa  query irst to determine which of the fields contain 4

 

??

 

[pocobueno1388]

Yes it is.

 

Are the "fields" from the database, or a form?

[Mutley]

It's a database.

 

I have a terrible feeling this is going to be a really easy/obvious solution.

[Mutley]

Would I do it using LIKE or AND etc?

[Barand]

If those are int fields it may not accept '00', only '0'. But, if they are varchar,

 

UPDATE mutley SET
  field0 = IF(field0='4', '00', field0),
  field1 = IF(field1='4', '00', field1),
  field2 = IF(field2='4', '00', field2),
  field6 = IF(field6='4', '00', field6)

 

This will change the values in every row unless you specify a WHERE clause to state which rows to update

 

[Mutley]

How would I do a where clause?

 

They are INT, the 00 was just an example to stand out.

 

I want to update it like this:

 

$sql = "UPDATE table SET "the field" = '999' WHERE "the fields" = '4';

 

So it has to find the "4" in the 4 different fields (only 1 field will contain it, it's unique, like a user_id) then change it.

 

Hope that makes sense.

[GingerRobot]

I might be missing the point completely, but dont you just list the fields and use or?

 

$sql = "UPDATE table SET the field = 999 WHERE field1=4 OR field2=4 OR field3=4 OR field4=4";

[Barand]

if you have

[pre]

+----+--------+--------+--------+--------+

| id | field0 | field1 | field2 | field6 |

+----+--------+--------+--------+--------+

|  1 | 4      | 3      | 5      | 6      |

|  2 | 3      | 4      | 5      | 6      |

|  3 | 1      | 3      | 4      | 7      |

|  4 | 1      | 2      | 0      | 4      |

+----+--------+--------+--------+--------+

[/pre]

 

and you only want to update the row where the id is 3

 

UPDATE mutley SET
  field0 = IF(field0=4, 999, field0),
  field1 = IF(field1=4, 999, field1),
  field2 = IF(field2=4, 999, field2),
  field6 = IF(field6=4, 999, field6)
WHERE id = 3

 

-->

[pre]

+----+--------+--------+--------+--------+

| id | field0 | field1 | field2 | field6 |

+----+--------+--------+--------+--------+

|  1 | 4      | 3      | 5      | 6      |

|  2 | 3      | 4      | 5      | 6      |

|  3 | 1      | 3      | 999    | 7      |

|  4 | 1      | 2      | 0      | 4      |

+----+--------+--------+--------+--------+

[/pre]

 

[Mutley]

Awesome, thanks!