Menu
I would like to write a stored procedure where I can update a table based on following criteria.
Table
EmployeeID GroupID Group#
123 G123 3
456 G456 3
789 G789 3
101 G101 3
View
GroupID_Granular GroupID_Middle GroupID_Executive
G123 M123 E123
G789 M789 E789
If GroupID is found in View’s GroupID_Granular column, update the table, set GroupID = GroupID_Executive and set the Group# to 1.
I am not sure how to check/compare and then run Update cmd.
Thank you
492
SQL UPDATE Syntax To use the UPDATE method, you first determine which table you need to update with UPDATE table_name . After that, you write what kind of change you want to make to the record with the SET statement. Finally, you use a WHERE clause to select which records to change.
Comparing the Results of the Two Queries Let us suppose, we have two tables: table1 and table2. Here, we will use UNION ALL to combine the records based on columns that need to compare. If the values in the columns that need to compare are the same, the COUNT(*) returns 2, otherwise the COUNT(*) returns 1.
As a result, SQL doesn't have the problem of ambiguity of = meaning either assignment or equality check. As a result, there is no problem with using = to check equality. On the other hand, in a programming language such as Java, single = is used for assignments, while == is used for comparison.
This can be done easily with a JOIN:
UPDATE t
SET
t.GroupID = v.GroupID_Executive,
t.[Group#] = 1
FROM YourTable t
JOIN YourView v ON v.GroupID_Granular = t.GroupID
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
