SQL People

[BDawg]

I know the basics of SQL, but am not the best with some more advanced concepts. I'm trying to create a script that will do the following where it will pull variable "A" from a list and execute until it reaches EOF.

update tablex set fieldy = 'xxx' where fieldz = A

The file will just have a whole list of A values, each of which will need fieldy set to 'xxx.'


How would I script this? It's a SQL Server 2000 server and I'm using SQL Query Analyzer.

 

[KLin]

You can write some VBA in access that will do that.

 

[BDawg]

That will edit it in a SQL db? How easy is that?

 

[KLin]

It depends on where the variable is coming from. do you want the user to put it in or do you want the variable pulled from a list within a table? Here's what I'd do

1. link the SQL table within Access via ODBC
2. create a form
3. create a command button
4. put the attached code in the On click event


None of this has been tested though. Use at your own risk 🙂.



woops, you did say list. My bad 😱

 

[notfred]

psuedocode:

string query = "update tablex set fieldy = 'xxx' where fieldz = "

for each line in file{

if (line != last line)
{
query += "line or "
}
else
{
query += "line"
}

}

run("query");

 

[ncage]

BDawg...im a veteran user of SQL Server. I think your going about this the wrong direction. Can you explain exactly what you are doing. Why are you putting these values in a file? Don't put stuff in a file unless you have to Create a temperary table to hold these values. Say you create a temperary table to put these values in call it MyTempTable which is suppose to update your main table call it MyTable...all you would have to do in the update would be:

Update MyTable
set fieldy = b.fieldy
From MyTable a
Inner Join MyTempTable b
on a.fieldz = b.fieldz

Your going to have to get me some more information on exactly whats going on here for me to help you but i would recommend staying away from the file unless you have to....and if you have to do the file i would still insert them into a temp table than do the update like i outlined above.

 

[BDawg]

We have approximately 1000 entries in our database that had a value of say X assigned to one field where the value should be Y. I have a list of the ids of each of the entries from a report, I just have to change that one field of X to Y in each of them.

There are probably 50,000+ other entries in this table with the correct value that does not need changed.