0

I want to add a record into access. The column name is Names: Now I want to add data into existing data, without deleting or adding the existing record

suppose:

id name     original name
1  blue       shoes   
2  black      shoes 
3  green      shoes

Now I want it like this suppose the record one is already there, and when user add the next two entries it should be like this

moreover: if a user send a new value to the column_name, so the value must be add to the name column without omitting the other value. If it is like blue and you send name value = black as a new value so it should look like blue black

id name    original name
1  shoes   blue black

So how can I do this with an SQL statement

BIBD
  • 15,107
  • 25
  • 85
  • 137
safi
  • 3,636
  • 8
  • 24
  • 37
  • Either I'm missing something or this is really unclear. You want to use an `INSERT` statement to transpose two fields, concatenating the values of one of them into a single value, removing the extra records after the concatenation, and preserve the original ID of the field being modified? What? – David Feb 16 '11 at 13:24
  • Don't understand what you want to do, could you elaborate some more? – gjvdkamp Feb 16 '11 at 13:24
  • Your question makes no sense to me, but it sounds like you want to learn about `UPDATE` http://msdn.microsoft.com/en-us/library/bb221186.aspx – Gabe Feb 16 '11 at 13:26
  • 2
    If the `original name` column is supposed to be an audit trail of some sort, you may want to move that to its own table. Each record in the audit table would record exactly one change and include a timestamp, maybe a user ID, etc. That way the audit data would be useful. – David Feb 16 '11 at 13:27
  • @David, yes, if a user send a new value to the column_name, so the value must be add to the name column without omitting the other value. if it is like blue and you send black as a new value so it should look like blue black. – safi Feb 16 '11 at 13:34
  • 1
    @safi: Is there a compelling reason to store the history information like this? A separate audit table would really make it more relational, which is what a relational database is meant to do. – David Feb 16 '11 at 13:39
  • @David, Well i will say yes because i have done as you saying, but when i type for more than one keywork(in this case name) then it retrieve 1 result. so basically my search is limited to just one word and i want to make it atleast 4. – safi Feb 16 '11 at 13:44
  • @safi: It's very possible to select more than one record from an audit trail table. – David Feb 16 '11 at 13:46
  • `string [] car_name; string allnames = "'" + String.Join("','", car_name) + "'"; for (int k = 0; k < car_name.Length; k++) { oledbcommand cmd1= new oledbcommand("select * from table1 where name in (" + allnames + ",myconnection) cmd1.exectuenonquery();` I have earlier used this query to search in and return multiple files but this didnt helped me. – safi Feb 16 '11 at 13:57

2 Answers2

1

Something like this should do it

UPDATE tbl SET tbl.[original name] = tbl.[original name] + ' ' + @newName WHERE tbl.[name] = 'shoes'

You are losing all the relational goodness in the database however. So you probably want to take a long hard look at your design here and see if it can be improved (it almost certainly can).

James Gaunt
  • 14,631
  • 2
  • 39
  • 57
  • 1
    You cannot use named parameters with Jet/ACE and OleDB, you must use a question mark and add the parameters in the order in which they occur. – Fionnuala Feb 16 '11 at 13:26
  • @James Gaunt: so this query will not replace the value of the record, but will add data to the exiting record, like if it contains blue, and i send a value of black so it will be like blue black? – safi Feb 16 '11 at 13:26
  • Subject to @Remou's point on using parameters in OleDB then yes it will. I only use MSSQL so can't help on exactly how to pass a parameter into the query from outside. – James Gaunt Feb 16 '11 at 13:28
  • You would need SET in there. `UPDATE tbl SET tbl.[original name] =` – Fionnuala Feb 16 '11 at 13:29
  • @Remou's: I am using parameter and using jet/acc with oledb and it works fine: sqlcmdCommand.Parameters.AddWithValue("",""); – safi Feb 16 '11 at 13:30
  • @Safi There may be some cases where it is possible to use named parameters (not parameters in general), but in many cases you cannot, for example: http://stackoverflow.com/questions/1476770/oledbcommand-parameters-order-and-priority – Fionnuala Feb 16 '11 at 13:46
0
update names set original_name = name where id = 1;

update names set name = "shoes" where id = 1;

and of course replace the id with the actual id and "shoes" with user input.

Henrik
  • 3,714
  • 1
  • 18
  • 20
  • this is easy to be done, but what i want to do is to add data to the existing name column. without loosing any data – safi Feb 16 '11 at 13:32