CODEWITHSUNDEEP
t-sqlsql-update
user797257
user797257

Reputation:

Update multiple rows by values taken from another table

Here is what I could master so far:

declare @i int = 7;
declare @lat float;
declare @lng float;
declare @point ???; -- what type?

declare @location table(lat float, lng float)

insert @location values (32.097218, 34.770438199999944)
insert @location values (32.083847, 34.775618)
insert @location values (32.1600788, 34.771902)
insert @location values (31.9914283, 34.80780099999993)
insert @location values (32.1574281, 34.775191800000016)
insert @location values (29.5543212, 34.89448429999993)
insert @location values (32.8018919, 34.96268420000001)

while @i > 0
    begin
            -- this is a wrong way to do it
        set @point = (select top (1) * from @location);
            -- must set @lat and @lng here somehow, based on the
            -- one row selected above. Deleting here is not
            -- mandatory, but may be used, if needed.
        delete top (1) from @location;
        update top (1) rest_tbl
        set lat = @lat, lng = @lng
        where lat is null and private_label_id = 3
        set @i = @i - 1
    end;

Please don't mind the part, where I'm creating the @location variable - in the real world, it is an actual table, I'm just using it for PoC.

Upvotes: 0

Views: 1287

Answers (2)

Kaf
Kaf

Reputation: 33809

I think this is what you need. If you are on sql-server 2005 or above you could use CTE and row_number() function as below without a loop. Please replace cols_that_decides_order with correct column(s) to get the top records.

Also I think your lng will be rounded up when converting to float (ex; 34.770438199999944 >> 34.7704382). 

--Declare the table with auto incremented identity with seed=7 and increment = -1.
declare @location table(mykey int identity(7,-1), lat float, lng float)

insert @location values (32.097218, 34.770438199999944)
                       ,(32.083847, 34.775618)
                       ,(32.1600788, 34.771902)
                       ,(31.9914283, 34.80780099999993)
                       ,(32.1574281, 34.775191800000016)
                       ,(29.5543212, 34.89448429999993)
                       ,(32.8018919, 34.96268420000001)

;with cte as (
   select lat,lng, row_number() over (order by cols_that_decides_order) rn
   from rest_tbl
   where lat is null and private_label_id = 3
)
update c set c.lat = t.lat, c.lng = t.lng
from cte c join @location t on c.rn = t.myKey

Upvotes: 1

Maryam Arshi
Maryam Arshi

Reputation: 2024

I think Using Cursor in this matter will help

DECLARE db_cursor CURSOR FOR (Select  lat,lng  from @location
where lat not in (Select lat from rest_tbl where lat is not null));

DECLARE @lat Float;
DECLARE @lng Float;

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @lat,@lng;
WHILE @@FETCH_STATUS = 0  
BEGIN  
        UPDATE top (1) rest_tbl
        SET  lat =@lat,lng =@lng
        where lat is null

       FETCH NEXT FROM db_cursor INTO @lat,@lng;
END;
CLOSE db_cursor;

Delete from  @location
    where lat in (Select lat from rest_tbl where lat is not null)

Upvotes: 1

Related Questions