SQL statement help, 5 minutes of your help. paying 5 bucks

paying 5 bucks for 5 a few minutes of your time if you know sql good.

I need a statement. i will give you the scenario.

I have a database.
There is a table called Table_1
In this table there is a column called Active_inactive which will have either an I or A in it for each record.

I have 50 documents I want to change this record for, from I to A.
I have to query the db with a couple of variables...
By project ID (the database has thousands of projects each with unique id called Project_id)
There is also another column called 'revisions'
This can either have an A,B, or C.
I need only the docs that have A.

So....

Find all 50 documents (with specific names, lets just say numbered 1-50) in specific project with A in the revisions column and I in the Active_Inactive column, then replace all the I's with A's in the revisions column.


anyone ?

thanks
jay

 

Is this MySQL or MSSQL?

Something like this should work:

UPDATE Table_1
SET Active_Inactive = 'A'
WHERE Project_id = 'WHATEVER ID YOU WANT'
AND revisions = 'A'
AND Active_Inactive = 'I';

Not sure about the project id part. DO you need to update from a specific list, or do they all have the same Project_id?

 

all different project id's

but what about the document names ?
its mssql

 

anyone ? jestep ?

 

Agree the above should update the active column for ALL documents where the document is currently INACTIVE and revision is A. You stated that you have 50 documents that need to be updated; can I assume there are other documents that meet the above criteria that you do not want to update? If so, you must change the expression in the WHERE clause.

UPDATE Table_1
SET Active_Inactive = 'A'
WHERE revisions = 'A'
AND Active_Inactive = 'I'
AND DocumentId IN ([insert document ids here]);

OR

UPDATE Table_1
SET Active_Inactive = 'A'
WHERE revisions = 'A'
AND Active_Inactive = 'I'
AND DocumentId IN (SELECT DocumentId FROM Table_1 WHERE [insert expression here]);

 

why the () ?
wouldnt it be like this, the last line ?
AND Document_column = '1.jpg,2.jpg,3.jpg';

is a space after each comma ?

each doc updated is located in here
WHERE Project_id = 'WHATEVER ID YOU WANT'

so only docs in that project will be updated so no need for worrying about other docs

 

When using the IN operator, you are specifying a list of values.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

The syntax is IN ( [insert a comma-delimited list of values to match] ). The way you wrote it will not give you the results you want, because it is trying to match where Document_column is equal to '1.jpg,2.jpg,3.jpg'; I hope that makes sense.

 

ok so it should be

AND Document_column IN ([1.jpg,2.jpg,3.jpg]);

whole statement will be....

UPDATE Table_1
SET Active_Inactive_column = 'A'
WHERE Project_id-column = '502998'
AND revisions_column = 'A'
AND Active_Inactive_column = 'I'
AND Document_column IN '([1.jpg,2.jpg,3.jpg]);


I just read it should be like this...

AND Document_column IN ('1.jpg','2.jpg','3.jpg');

 

Drop the "[ ]" brackets from the IN operator list. I was using them to denote a list of values. Also, if the values you are matching are text be sure to enclose them in quotes. For example '1.jpg', '2.jpg', 3.jpg'. If you have 50 documents for each project, this is going to be a challenge. I would consider using a sub-query to provide the list of values for the IN operator.

AND Document_column IN (SELECT Document_column WHERE expression).

Also, if you use a sub-query you might be able to drop the Project_id column from the above UPDATE statement.

 

ok, i think i am set with this

UPDATE Table_1
SET Active_Inactive_column = 'A'
WHERE Project_id-column = '502998'
AND revisions_column = 'A'
AND Active_Inactive_column = 'I'
AND Document_column IN ('1.jpg','2.jpg','3.jpg');

the docs are images so it will be just like that, its 50 but i dont mind typing it all in.

thanks, shoot me a pm with your paypal id, ill send the 5 bucks. thanks.
by the way, any way to test this for syntax errors ?