SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Need help with SQL Server 2008 UPDATE
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SamFarr
Starting Member

United Kingdom
1 Posts

Posted - 04/28/2014 :  06:46:34  Show Profile  Reply with Quote
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 statement
SELECT     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 Expr9
FROM         user_group CROSS JOIN
                      Consultants
WHERE     (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 is

UPDATE    user_group
SET              user_group.description = Consultants.description
FROM         user_group 
INNER JOIN Consultants
ON user_group.description = consultants.description
WHERE 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

MuralikrishnaVeera
Posting Yak Master

India
107 Posts

Posted - 04/28/2014 :  07:51:04  Show Profile  Reply with Quote
Have you tried this ???

Specifying the table name FOR Column 'name'
[WHERE user_group.name like ''Adam Froth%]



---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17658 Posts

Posted - 04/28/2014 :  08:07:51  Show Profile  Reply with Quote
what is the full error message ?

what database are you using ?


KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000