Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    35

    Unanswered: updating a table

    I know this is probably really obvious but I am trying to insert values into columns into a table if a client exists and I can't for the life of me figure out the syntax. It is something like this:


    table A
    Client_Id Field 1 Field 2


    Insert (Field1) into Table_A
    Select field1 from table_B
    Where Table_B.Client_Id = Table_A.Client_id


    Thanks.

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well if the already exists, why do you want to add duplicates?

    Is there a unique index on client id?

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    
    CREATE TABLE myTable99(ClientId int)
    CREATE TABLE myTable00(ClientId int)
    GO
    
    INSERT INTO myTable99(ClientId)
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
    
    INSERT INTO myTable00(ClientId)
    SELECT 1 UNION ALL
    SELECT 2 UNION ALL
    SELECT 3 UNION ALL
    SELECT 4
    GO
    
    INSERT INTO myTable99 (ClientId) 
    SELECT ClientId 
      FROM myTable00 o 
     WHERE EXISTS (SELECT * 
    		 FROM myTable99 i 
    		WHERE i.ClientId = o.ClientId)
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2004
    Posts
    35
    I don't want to add duplicates. I want to update the record. I am trying to also match the row. I am basically trying to write an update query with multiple columns.

  4. #4
    Join Date
    Jan 2004
    Posts
    35
    Something like this:

    INSERT INTO Table1(Field1, field2,
    Field3, Field4, Field5, Field6, Field7,
    Field8)
    SELECT
    Field1, field2,
    Field3, Field4, Field5, Field6, Field7,
    Field8
    FROM
    dbo.table2
    WHERE
    Table1.Field1 = table2.field1

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    To do an update, you'd need to use it....not an insert

    Code:
    USE Northwind
    GO
    
    SET NOCOUNT ON
    
    CREATE TABLE myTable99(ClientId int, Col1 int)
    CREATE TABLE myTable00(ClientId int, Col1 int)
    GO
    
    INSERT INTO myTable99(ClientId, Col1)
    SELECT 1,1 UNION ALL
    SELECT 2,2 UNION ALL
    SELECT 3,3 UNION ALL
    SELECT 4,4
    
    INSERT INTO myTable00(ClientId, Col1)
    SELECT 1,4 UNION ALL
    SELECT 2,3 UNION ALL
    SELECT 3,2 UNION ALL
    SELECT 4,1
    GO
    
    SELECT * FROM myTable99
    SELECT * FROM myTable00
    
        UPDATE a
           SET a.Col1 = b.Col1
          FROM myTable99 a
    INNER JOIN myTable00 b
    	ON a.ClientId = b.ClientId
    GO
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    DROP TABLE myTable99
    DROP TABLE myTable00
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •