Select Update Query Help 1

[craigward]

Hi,

I have a query that joins two tables and takes the client_id from both tables and displays them next to each other.

What I need to do based on my WHERE clause is update the q.client_id with the values in c.client_id and trying to build a query to do that is doing my brain in.

Can anyone shed some light on the best way to achieve this? I would display all my failed attempts but that might not be helpful

Thanks for looking.

select c.company,q.createtime, Q.emp_FIRST_NAME, q.emp_lastname, q.client_id, c.client_id as contact_client_id from wce_contact c inner join wce_qualifications q on c.uniqueid = q.entityid where not c.client_id = q.client_id

 

[gmmastros]

What I need to do based on my WHERE clause is update the q.client_id with the values in c.client_id and trying to build a query to do that is doing my brain in.

If you are using SQL Server, the syntax to update the data in one table with data from another table is like this....

Update q
Set    q.client_id = c.client_id
From   wce_contact c 
       inner join wce_qualifications q 
         on c.uniqueid = q.entityid 
where  not c.client_id = q.client_id

The syntax may be different for other types of databases. Also, before you run this, you should make sure you have a good backup, just in case this does not do what you want it to.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[craigward]

Thank you very much for that. Worked a treat and taught me something.