omagarc
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.frombin ary(demogr aphics.bir thdate)
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..
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.frombin
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
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..
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
Buton Click Event.
{
Dim strFiter as String ="Table2.id='" table1.id "'"
Dim dRows() as DataRow=ds.Tables(table2).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wasn't exactly what I was looking for but put me on a path. Thanks!
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