2

I am trying to update values from one table to another in SQL Server. I have 2 tables: tblBus and tblRB_ID.

create table tblBus
(
    bus varchar (10) NOT NULL PRIMARY KEY,
    rb_id int FOREIGN KEY REFERENCES tblRB_ID(rb_id2),
    roadside_bottom varchar (20)
);
Bus rb_id roadside_bottom
1000 NULL NULL
1001 NULL NULL

Here is tblRB_ID:


create table tblRB_ID
(
    rb_id2 int NOT NULL IDENTITY (1,1) PRIMARY KEY,
    contract_id int FOREIGN KEY REFERENCES tblContracts(contract_id),
    rb_name varchar (20)
    CONSTRAINT rb_pass CHECK
    (
        rb_name IN ('King', 'Super King', 'Kong', 'Half Side')
    )
);
rb_id2 contract_id roadside_bottom
8 1 King
9 2 Super King

I tried to update the values using this, but I just get an error saying "Incorrect syntax near the keyword 'RIGHT'".

UPDATE tblBus
RIGHT JOIN tblRB_ID
ON tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name;
SET tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name;

I want to tblBus to updated with the information from tblRB_ID. I want both records in tblRB_ID to be into tblBus, and any subsequent record that I put into tblRB_ID. I want it to look like this:

Bus rb_id roadside_bottom
1000 8 King
1001 9 Super King

Please help.

Thank you

LongLouis
  • 41
  • 3
  • Have a look at the [syntax](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#syntax) and the [Examples](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#UpdateExamples) (such as [this one](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#l-specifying-a-table-alias-as-the-target-object)) in the documentation, and you'll see your `UPDATE` is quite malformed. The `JOIN`s are *before* the `SET` and you are completely missing a `FROM`. – Thom A Sep 17 '21 at 10:26

1 Answers1

2

the syntax you are using is different. the update syntax is like below

UPDATE <Table Alias>
SET
    <Column to be updated> = <Column from which you need to update>
    FROM TableA a
        INNER JOIN TableB B
            ON A.<Column Name> = B.<ColumnName>

So your query should actually be like

UPDATE tblBus
SET tblBus.rb_id = tblRB_ID.rb_id2,
   tblBus.roadside_bottom = tblRB_ID.rb_name
  FROM tblBus
        JOIN tblRB_ID -- Inner join is recommended so that the non-matching rows are not updated with NULL
            ON tblBus.rb_id = tblRB_ID.rb_id2
                AND tblBus.roadside_bottom = tblRB_ID.rb_name;
Jayasurya Satheesh
  • 7,826
  • 3
  • 22
  • 39