Solved

SQL Update\Set of a field in one table from the same field in a different table.

Posted on 2010-09-14
6
263 Views
Last Modified: 2012-06-21
I have a field that needs to be updated\set in Table.B from same field from Table.A.  This needs to be done with the following conditions.

I will write in kind of a  pseudo code format:

Table.B.DOCO = Table.A.DOCO
    Table.B.REF = "6F"                  
       Table.B.DATE = '09/14/2010'
          Table.A.Line = '1'

If the above finds records the Table.B.REF will need to be populated with Table.A.REF

0
Comment
Question by:SnowLapa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 20

Expert Comment

by:Silvers5
ID: 33678219
You can setup a trigger in SQL, when the above conditions are met it will insert the record in table A
0
 
LVL 20

Expert Comment

by:Silvers5
ID: 33678225
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 200 total points
ID: 33678254
If I understand your requirements this is an update you need to run, not a need to keep them in sync continuously.  If so, this should do the trick.

update b
set ref = a.ref
from tableb b
join tablea a
  on b.doco = a.doco
where b.ref = '6F'
  and b.[date] = '2010-09-14' -- changed to non-ambiguous format
  and a.line = '1'

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:SnowLapa
ID: 33678257
I am not very familiar with triggers and I think the learning curve would be to great.  I am a little more familiar with standard SQL statements.  Something more along those line would be great.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 33678295
>>I have a field that needs to be updated\set in Table.B from same field from Table.A. <<
Don't.  That is a nighmare.  Use a SELECT instead, somthing like:
SELECT  a.ref, b.Col1, b.Col2, b.Col3, ...
FROM    TableA a
        INNER JOIN TableB b ON a.doco = b.doco
WHERE   a.line = '1'
        AND b.ref = '6F'
        AND b.date = '2010-09-14'

Open in new window

0
 

Author Comment

by:SnowLapa
ID: 33681929
Thank you all for the speedy solutions.  BrandonGalderisi solution did work with just a few tweaks.  

Once again, thank you all......
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question