Hi everyone, I'm new to the forum, so apologies if I am posting this in the wrong place!I've just written a query that successfully brings back the data from one table based on the information from another. Basically we have been given a table of information and need to update certain fields in our user_group table with the new info.Here is the SELECT statementSELECT user_group.id, user_group.name, user_group.description, Consultants.description AS Expr10, user_group.btype, user_group.rootmenu, user_group.intra_user, user_group.primary_g_id, user_group.fname, user_group.lname, user_group.ntlogon, user_group.lang_id, user_group.[external], user_group.title, user_group.work_tel, user_group.work_fax, user_group.work_ext, user_group.mobile, user_group.sex, user_group.add2, user_group.add3, user_group.town, user_group.county, user_group.pcode, user_group.private_flag, user_group.department, user_group.image, user_group.website, user_group.account_ref, user_group.assistant, user_group.payroll_no, user_group.ug_ntupdate, user_group.room_no, user_group.floor_level, user_group.building, user_group.it_equip_no, user_group.postal_location, user_group.ug_spare7, user_group.ug_spare8, user_group.ug_spare10, user_group.comments, user_group.agency, user_group.memb, user_group.ss_contact, user_group.contact_owner, user_group.spare_int, user_group.status_id, user_group.co_type_vac, user_group.email, user_group.add1, user_group.in_out_id, user_group.ext_number, user_group.bus_type, user_group.manager_id, user_group.skype_user_name, user_group.preferred_name, user_group.bleep, user_group.directorate_id, user_group.dept_service_id, user_group.team_ward_id, user_group.site_id, user_group.password, user_group.secret_word, user_group.secret_word_last_used_date, user_group.login_name, user_group.image_id, user_group.sql_user, user_group.last_updated, user_group.hr_staff_number, user_group.hr_line_manager_id, user_group.restrict_login_by_ip, user_group.ug_spare4, user_group.s_last_updated, user_group.group_type_handle, user_group.import_def_id, user_group.weather_location, user_group.fax, user_group.last_email_captured, user_group.locked_out, user_group.pwd_expiry_days, user_group.pwd_last_reset, user_group.last_good_logon, user_group.gt_filter, user_group.home_tel, user_group.pager, user_group.dob, user_group.co_type_ts, user_group.created_date, user_group.modified_date, user_group.knowledge_id, user_group.primary_org_id, user_group.home_email, user_group.primary_manager_id, user_group.skills, user_group.rank, user_group.home_fax, user_group.ninum, user_group.marstat, user_group.country, user_group.map, user_group.rapiddial, user_group.cv, user_group.category, user_group.alternatephone, user_group.dispatch_comp, user_group.external_supp, user_group.job_recipient, user_group.job_recipdesc, user_group.last_inv_no, user_group.supervisor, user_group.ug_innt, user_group.ug_hrupdate, user_group.ug_inhr, user_group.ug_spare9, user_group.cat_id, user_group.o_hours, user_group.memb_fee, user_group.other_c, user_group.ss_ref, user_group.pref_cont_meth, user_group.d_park, user_group.d_acc, user_group.d_toilet, user_group.services, user_group.job_title, user_group.club_details, user_group.top50, user_group.person_type, user_group.Interests, user_group.Status_text, user_group.supp_due_date, user_group.direct_number, user_group.supported, user_group.client_id, user_group.co_type, user_group.timetest, user_group.Status_name, user_group.newfield1234, user_group.man_new, user_group.language, user_group.testbit, user_group.councillor_details, user_group.tel_list, user_group.dx_number, user_group.in_out_html_region, user_group.status, user_group.sms_virgin_mobile, user_group.sms_virgin_textarea, user_group.ec_image, user_group.include_on_extranet_front, user_group.legal_contact, user_group.legal_specialty_id, user_group.init_contact, user_group.l_speciality, user_group.l_main_contact, user_group.l_firm_used_by, user_group.UserType, user_group.region, user_group.file1, user_group.file2, user_group.file3, user_group.car_reg2, user_group.car_reg3, user_group.myteam, user_group.based_in, user_group.like_to_be, user_group.like_to_be_why, user_group.life_lesson, user_group.most_happy, user_group.best_text, user_group.parent_told_me, user_group.sport1, user_group.sport2, user_group.sport3, user_group.secret_talent, user_group.lookalike, user_group.image_baby, user_group.toms_talent, user_group.consult_days, user_group.version, user_group.remote_login, user_group.job_type, user_group.sql_ver, user_group.win_ver, user_group.apdf_ver, user_group.sorce_ver_date, user_group.in_out_loc, user_group.holiday_allocation, user_group.uprn, user_group.martyns, user_group.employee_number, user_group.my_profile, user_group.business_unit, user_group.your_location, user_group.location, user_group.T2Code, user_group.MCSCoCode, user_group.MCSAccountNo, user_group.htmlregion, user_group.AppKey1, user_group.AppKey2, user_group.ug_spare1, user_group.NewsGroup, user_group.MCSServer, user_group.cust_manager, user_group.manager, user_group.section, user_group.unit, user_group.desk_location, user_group.branch, user_group.centre_id, user_group.vanco_user_id, user_group.vanco_centre_id, user_group.car_reg_2, user_group.dxnumber, user_group.CCS_Approver, user_group.holiday_reset_date, user_group.office, user_group.car_reg, user_group.location_id, user_group.nextof_kin, user_group.function_id, user_group.ntpassword, user_group.holiday_remaining, user_group.holiday_area_code, user_group.signoff, user_group.phonebookbg, user_group.show_in_group_dir, user_group.parent_co_id, user_group.discipline_id, user_group.user_type, user_group.vat_status, user_group.CompanyRefNo, user_group.CompanyRegNo, user_group.Birthday, user_group.image_thumb, user_group.crisis_management, user_group.on_call, user_group.information, user_group.short_mobile, user_group.map_url, user_group.jobdescription, user_group.live, user_group.image_baby_thumb, user_group.image_approve_store, user_group.image_baby_approve_store, user_group.image_approval_required, user_group.image_baby_approval_required, user_group.image_approve_thumb, user_group.image_baby_approve_thumb, user_group.Holiday_lieu, user_group.Holiday_days, user_group.MySpace, user_group.Experience, user_group.personal_mobile, user_group.home_mobile, user_group.knolwedge_id, user_group.ShortCode, user_group.Weather_Region, user_group.StaffMember, user_group.Staff_Show, user_group.Book_Club, user_group.Processing_Centre, user_group.org_chart_image, user_group.job_description, user_group.broker_logo, user_group.company_name, user_group.weather_code, user_group.default_office, user_group.case_tracking_type, user_group.fsa_number, user_group.Dpa_agreement, user_group.company_id, user_group.nhs_site_id, user_group.nhs_location_id, user_group.nhs_ext, user_group.nhs_floor, user_group.sita_address, user_group.handling_agent_flag, user_group.xmas_list, user_group.No_dist, user_group.Location_acl_staff, user_group.Lastupdate, user_group.Country_code, user_group.CarrierCode, user_group.Owner, user_group.vWork_tel, user_group.vddi_phone, user_group.airline_code, user_group.hr_mname, user_group.hr_join_date, user_group.hr_disability_flag, user_group.hr_partner_type, user_group.hr_partner_dob, user_group.hr_partner_relationship_dob, user_group.hr_partner_status, user_group.hr_department_id, user_group.hr_category_id, user_group.hr_office_location_id, user_group.hr_first_aider, user_group.hr_fire_warden, user_group.hr_home_address, user_group.hr_orgin_id, user_group.hr_partner_name, user_group.hr_job_title_id, user_group.hr_car_make, user_group.hr_car_registration, user_group.hr_car_make_2, user_group.hr_car_registration_2, user_group.updated, user_group.holiday_days_hours, user_group.nyk_location_id, user_group.leaving_date, user_group.ug_spare2, user_group.ug_spare3, user_group.ug_spare5, user_group.ug_spare6, user_group.datetest, user_group.MailSent, user_group.MailSentDate, user_group.MailResponse, user_group.MsgID, user_group.desk_no, user_group.floorplate_no, user_group.speed_dial, user_group.business_area, user_group.responsibility, user_group.room_number, user_group.employee_id, user_group.staff_id, user_group.student_id, user_group.college, user_group.area, user_group.position, user_group.role, user_group.roomno, user_group.contact_role, user_group.translators, user_group.type, user_group.sub_type, user_group.updated_by, user_group.image_thumbnail, user_group.image_normal, user_group.UpdateDate, user_group.active, user_group.TeamID, user_group.mobile_vis, user_group.Start_date, user_group.employment_start_date, user_group.country_id, user_group.Country_Mgr_id, user_group.org_chart_team, user_group.PIL_Lang_id, user_group.export_format, user_group.channel_id, user_group.channel, user_group.web_view, user_group.web_category, user_group.directions, user_group.directions_attach, user_group.web_country_group_id, user_group.web_comment2_lang, user_group.web_comment1, user_group.web_comment1_lang, user_group.web_comment2, user_group.web_re, user_group.web_up, user_group.web_ra, user_group.web_head_office_id, user_group.web_comment3_lang, user_group.web_comment3, user_group.operations_director, user_group.branch_manager, user_group.assistant_manager, user_group.sales_representative1, user_group.sales_representative2, user_group.sales_representative3, user_group.add4, user_group.Free_Tel, user_group.Free_Fax, user_group.branch_id, user_group.speeddial, user_group.branch_folder_id, user_group.test, user_group.known_as, user_group.branch_map, user_group.department_info, user_group.company_status_id, user_group.org_type_id, user_group.office_id, user_group.Commission_bank_account_number, user_group.Commission_sort_code, user_group.address, user_group.sap_id, user_group.sap_location_id, user_group.sap_line_manager_sorce_id, user_group.profession_id, user_group.language_id, user_group.oasis_practice_id, user_group.oasis_cluster_id, user_group.oasis_department_id, user_group.oasis_job_type_id, user_group.oasis_practice_manager_id, user_group.champions, user_group.division_id, user_group.dont_show_in_contacts, user_group.service_area_id, user_group.meeting_groups_id, user_group.contactnumber, user_group.initials, user_group.organisation, user_group.service_users, user_group.service_type_id, user_group.cdd_image, user_group.updated_details, user_group.job_title_role, user_group.job_role_type, user_group.primary_g_new_id, user_group.department_id, user_group.Contact_number, user_group.Committee_number, user_group.Salutation, user_group.PreferredName, user_group.primary_link_id, user_group.oasis_practice_code, user_group.oasis_practice_localname, user_group.oasis_date_joined, user_group.oasis_number_surgeries, user_group.oasis_number_patients, user_group.oasis_monday_opening, user_group.oasis_tuesday_opening, user_group.oasis_wednesday_opening, user_group.oasis_thursday_opening, user_group.oasis_friday_opening, user_group.oasis_saturday_opening, user_group.oasis_sunday_opening, user_group.oasis_pct, user_group.oasis_contract_targets, user_group.oasis_grosscontract_value, user_group.oasis_uda_value, user_group.oasis_uda_allocation, user_group.oasis_onsite_parking, user_group.oasis_disabled_access, user_group.oasis_clinicalserv_specialisations, user_group.oasis_referrals, user_group.oasis_practice_man_system, user_group.primary_practice_id, user_group.primary_practice_stream_id, user_group.oasis_primary_contactid, user_group.oasis_animal, user_group.oasis_gdc_registration_number, user_group.oasis_nhs_performer_number, user_group.oasis_vendor_number, user_group.oasis_practice_email, user_group.oasis_uoa_allocation, user_group.oasis_uoa_value, user_group.oasis_usa_value, user_group.oasis_usa_allocation, user_group.oasis_responsibilities, user_group.oasis_hobbies, user_group.oasis_personal_achievement, user_group.oasis_to_do_before_next_year, user_group.oasis_public_liability_number, user_group.oasis_public_liability_number_valid, user_group.oasis_active_status, user_group.oasis_hepb_from, user_group.oasis_hepb_to, user_group.oasis_team_charter, user_group.hr_car_colour, user_group.oasis_patient_private, user_group.oasis_patient_denplan, user_group.oasis_patient_other, user_group.oasis_patient_type_nhs, user_group.oasis_referral, user_group.shelter_team_id, user_group.shelter_job_id, user_group.shelter_pers_ref, user_group.shelter_staff_id, user_group.curr_proj, user_group.age, user_group.disability, user_group.gender, user_group.sexual_orientation, user_group.race, user_group.religion_and_belief, user_group.cestria_tenant, user_group.marketingId, user_group.PctId, user_group.Race2, user_group.Fish, user_group.Contract_No, user_group.Bryns, user_group.Cost_Centre, user_group.Request, user_group.Manager_Name, user_group.region_id, user_group.contact_name, user_group.aka, user_group.postcode, user_group.bank_details, user_group.Bank, user_group.Bank_Address, user_group.Account_Name, user_group.Account_Number, user_group.Kin_name, user_group.Kin_address, user_group.kin_homeph, user_group.kin_workph, user_group.kin_relation, user_group.emergency_person, user_group.emer_person_addr, user_group.emer_person_homeph, user_group.emer_person_workph, user_group.emer_person_rel, user_group.ethnicity, user_group.workplace, user_group.extranet_last_updated, user_group.mobile_rootmenu, Consultants.fname AS Expr1, Consultants.lname AS Expr2, Consultants.name AS Expr3, Consultants.description AS Expr4, Consultants.site, Consultants.department AS Expr5, Consultants.Secretary, Consultants.work_tel AS Expr6, Consultants.mobile AS Expr7, Consultants.postal_location AS Expr8, Consultants.email AS Expr9FROM user_group CROSS JOIN ConsultantsWHERE (user_group.name LIKE Consultants.name) AND (user_group.btype = 'u ') AND (user_group.description LIKE '%' + Consultants.description + '%') AND (user_group.intra_user = 1) AND (user_group.primary_g_id IS NOT NULL)ORDER BY user_group.name
We want to update the 'description' on the user_group table with the 'description' from the 'consultants' table. To test this, we only want to write the UPDATE so that it changes the description where the name is 'Adam Froth. The UPDATE statement that we've written isUPDATE user_groupSET user_group.description = Consultants.descriptionFROM user_group INNER JOIN ConsultantsON user_group.description = consultants.descriptionWHERE name like 'Adam Froth%'
but it keeps erroring and saying that it could 'Not be bound'.Apologies for posting such a large topic on my first post! Any help is appreciated, we are in a really tight spot!Many thanks,Sam
Need help with SQL Server 2008 UPDATE
Posted - 2014-04-28 : 06:46:34
