Looking for a simple SQL query

[kevinkhan]

How do i complete this query

 

i want the published column to go to 0 when all the published column is gone to one..

 

UPDATE Classified SET `published`=0 WHERE productid=2 AND

[Adam]

You mean..?

 

UPDATE Classified SET `published`=0 WHERE `published`=1

[kevinkhan]

You mean..?

 

UPDATE Classified SET `published`=0 WHERE `published`=1

 

no sorry i only want to update when all entries are published = 1

like when count(published=0) = 0

[ManiacDan]

Wait, so you want ALL rows to set published=0 when NO rows are published=1?

 

By virtue of binary logic, the database already does this.  If no rows are published=1, and the only two values are 0 and 1, then all the rows are already zero.

 

-Dan

[kevinkhan]

Wait, so you want ALL rows to set published=0 when NO rows are published=1?

 

By virtue of binary logic, the database already does this.  If no rows are published=1, and the only two values are 0 and 1, then all the rows are already zero.

 

-Dan

 

no i would like

ALL rows to set published=0 when ALL rows are published=1?

[ManiacDan]

You're going to have to do this in 2 queries.  You could theoretically do it in one, but it would be easier to just select count(*) and SUM(published) and, if they match, do a global update statement.

 

-Dan

[fenway]

I don't follow what's desired here.

[ManiacDan]

I don't follow what's desired here.

Once all articles are marked "published" he wants to "reset" the table so that they're all unpublished again.  In English, "Update the table, set published = 1 where ALL ROWS are published = 0." 

 

I can see why you're confused.

 

-Dan

[fenway]

Dare I ask why published becomes unpublished? Theoretically, this can be achieved with an UPDATE trigger -- but I don't think you can update the same table/record yet.