copy data from 1 table, column to another table column where ids are =

[cmec]

Newbie here.

a friend helped me but now need more help.

What I used to have was users would type in a date of a report was completed (dd/mm/yyyy) and it would be stored in a table called cwis_forms, in the column date_reported - data type varchar(15) and an id would be associated with it in the column irid

Now users do not have to type in the date_reported. This date is now automatically stored in a table named cwis_inspection_reports in a column named date_final_saved and is associated with the same id irid

I need to copy all the dates from the cwis_forms, in the column date_reported records to the cwis_inspection_reports in a column named date_final_saved based on the irid.

i assume it is something easy like copy this to that where the irid is equal. but I am not a db person so I need the actual statement.

 

[tshot]

it is not clear
a) table cwis_inspection_reports also includes id irid of cwis_forms.
b) data type of date_final_saved in cwis_inspection_reports
c) are u using PHP or ASP or simply mysql

these would be needed for ur solution




----------------
ur feedback is a very welcome desire

 

[cmec]

a) table cwis_inspection_reports also includes id irid of cwis_forms.
YES
b) data type of date_final_saved in cwis_inspection_reports
SEE BELOW
c) are u using PHP or ASP or simply mysql
SIMPLY MYSQL


I just need to take care of the data conversion.

CODE I HAVE SO FAR, LOOKS GOOD?
UPDATE cwis_inspection_reports, cwis_forms
SET cwis_inspection_reports.date_final_saved = cwis_forms.date_reported
WHERE cwis_inspection_reports.irid = cwis_forms.irid


DATA TYPES, STORED IN DB THAT I NEED TO CONVERT
date_reported is "varchar 15" data type and in the database is stored as mm/dd/yyyy ie; 12/25/2003

final_saved_date is "date" data type and in the db is stored as yyyy-mm-dd ie; 2003-10-27

 

[ingresman]

Can you post the actual table defintions please. What would be usefull would be a couple of rows from the source table and what gets produced. When you store dates in mysql I recall they always get stored as yyyymmdd which may give odd results.

 

[tshot]

mysql stores date as yyyy-mm-dd and if the date is not in this form will give error

unfortunately mysql doest not have any string or date conversion functionwhich converts dd/mm/yyyy to yyyy-mm-dd
so we modify ur following line
[code[
SET cwis_inspection_reports.date_final_saved = cwis_forms.date_reported
[/code]
to
[code[
SET cwis_inspection_reports.date_final_saved = concat(right(cwis_forms.date_reported, 4),'-', mid(cwis_forms.date_reported, 4,2),'-', left(cwis_forms.date_reported,2))
[/code]

what i have done is taken out the year, combined with - , then month - and then date.

presumption is that ur original date has 4 digits for year, 2 for month. if not then change them first 2 dd/mm/yyyy. it would simply ur conversion






----------------
ur feedback is a very welcome desire