Solved

sql statement

Posted on 2006-07-09
10
230 Views
Last Modified: 2010-04-30
mysql 5.0


correct sql statemnt to change a column from yes to no
0
Comment
Question by:thenone
10 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 17070551
>correct sql statemnt to change a column from yes to no

put in here the correct name of the table and the columns:

UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `keyfield` = <keyvalue> AND `thecolumn` = 'yes'

0
 
LVL 8

Author Comment

by:thenone
ID: 17070591
thanks angel
0
 
LVL 8

Author Comment

by:thenone
ID: 17070592
and if I do not have a primary key?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17070642
>and if I do not have a primary key?
how do you identify the row to update then?
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17070660
UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `thecolumn` = 'yes'

This will update everything - regardless of the id
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 8

Author Comment

by:thenone
ID: 17073422
and if I was doing it with a recordset?
0
 
LVL 8

Author Comment

by:thenone
ID: 17076756
ok so if I have a recordset where it is rs.open"select * from table1 where column=no"

then when I am looping through my records how can I have the recordset be able to change that column one at a time from no to yes.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17077065
rs.open "select * from table1 where column=no"
while not rs.eof
   rs.fields("column").value = "yes"
   rs.update
   rs.movenext
wend
rs.close
0
 
LVL 8

Author Comment

by:thenone
ID: 17077115
so just this?

rs.fields("column").value = "yes"

ok thanks.
0
 
LVL 9

Accepted Solution

by:
lojk earned 250 total points
ID: 17080983
you would generally never do such a thing... It causes all sorts of record locking issues for starters not to mention the fact that it is *slow*

you would already have dimmed a connection to connect your rs anyway, so why not just do..

dim MyConn as new adodb.connection
myconn=whatever
myconn.execute ("UPDATE `yourtable` SET `thecolumn` = 'no' WHERE `keyfield` = <keyvalue> AND `thecolumn` = 'yes' ")

as angel had already posted?


also, if your DB is access and this is a boolean field rather than text (as it should be) you may actually need to do...

rs.fields("column").value = true


0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now