asked on

Global update of column based upon contents of another table

I need to update a table with a date field that is calculated from the contents of another table.

I have a table that stores ticks for a persons birthdate (demographics)

I want to be able to take the id and birthdate from the demographics table and update/insert into a second table with the
id = id
birthdate=datetime.frombinary(demographics.birthdate)

I've been able to create the connections to the tables but i have not been able to figure out how to just update the contents of a column based upon an expression withouth going through the records one by one.
Something like...
Do while .not. eof()
enter_begin(table2)
table2.id = table1.id
table2.birthdate = datetime.frombinary(table1.birthdate.tostring)
next
end while

I'm not much of a programmer and I was hoping vb 2008 would be easy but I appear to be hitting a big learning curve on "global" updates..

JimFive

Assuming that you are using a database to store your data you could just execute a pair of sql commands like:
UPDATE Table2 Set birthdate = (Select birthdate from table1 where Table2.id = table1id)

INSERT INTO TABLE2 (id, birthdate)
(Select id, birthdate from Table1 where Not exists (Select * from Table2 where table2.id = table1.id))
--
JimFive

omagarc

ASKER

Thanks but I'm asking for code that I can put in a button that will loop through the table until it reaches the end

AUmidh

Buton Click Event.
{

Dim strFiter as String ="Table2.id='" table1.id "'"
Dim dRows() as DataRow=ds.Tables(table2).Select(strFilter)
dim strBirthday as String=dRows(0)("Birthdate")

' Now you get strBirthDay make a query like

Dim strSQL as String ="UPDATE Table1 Set birthdate ='" + strBirthDay + "'"

' and using this strSQL you can update the Table2
}

ASKER CERTIFIED SOLUTION

AUmidh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

omagarc

ASKER

Wasn't exactly what I was looking for but put me on a path. Thanks!