Update table in sql query

Question

I am struggling with the following (apparently) easy task; my table (call it table1) look like this:

id | date | value ------------------ 1 | 01 | 100 1 | 02 | 103 1 | 04 | 105 1 | 05 | 90 1 | 06 | 95 1 | 09 | 0 2 | 02 | 110 2 | 03 | 98 2 | 04 | 97 2 | 07 | 71 2 | 08 | 84 2 | 10 | 0 ------------------

I would like to replace the two 0s with, respectively 95 and 84 (i.e. previous values in time). Any solution? I have been spending a looot of time on this (sorry but I am quite new to SQL)

Show what you have tried so far. And you will need 2 update statements. 1 for each value you want updated. — SS_DBA
– SS_DBA, Commented May 24, 2017 at 14:09
just do two queries, one where the ID=1 and Date=09 after you update that one write a second query where ID=2 and Date=10 it doesn't have to be complex — SaggingRufus
– SaggingRufus, Commented May 24, 2017 at 14:11
Do you want to update the records in your table or do you want to write a query where the zeros are replaced by the previous values in the results? — Thorsten Kettner
– Thorsten Kettner, Commented May 24, 2017 at 14:13
@WEI_DBA this is what I have tried: UPDATE table1 SET value = CASE WHEN value=0 THEN 'previous value in time' ELSE value END — v_emme
– v_emme, Commented May 24, 2017 at 14:37

nacho · Accepted Answer · 2017-05-24 15:08:45Z

1

Try this:

update table1 as a set a.value=(select b.value from table1 as b where b.date<=a.date order by b.date desc limit 1) where a.value=0;

Change to this

Make a new replica of table1 as table2 (same structure & same data in table1 and table2):

SET SQL_SAFE_UPDATES = 0; update table1 set value=(select value from table2 where table2.date<table1.date order by table2.date desc limit 1) where table1.value=0;

edited May 24, 2017 at 15:08

answered May 24, 2017 at 14:16

nacho

5,3772 gold badges28 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim Biegeleisen Over a year ago

If this actually runs without error it might be a valid approach.

v_emme Over a year ago

already used something similar and it didn't work. EDIT: tried also this and it does not work

Thorsten Kettner Over a year ago

@v_emme: Please never ever say "it didn't work" here without telling us what exactly happened. Did you get an error message? Were incorrect values written?

Strawberry Over a year ago

@ThorstenKettner You can't specify target table 'a' for update in FROM clause

Strawberry Over a year ago

Another join would probably fix it.

Thorsten Kettner · Accepted Answer · 2017-05-30 09:09:17Z

Building up on nacho's query:

MySQL's update is a bit flawed. First: it doesn't accept an alias for the updated table. Second: It complains when you access the same table you want to update in the query. The latter can easily be solved by replacing from table1 b with from (select * from table1) b.

nacho's statement also missed to have the subquery refer to the same ID. And he mistakenly included the record itself (b.date <= instead of b.date <).

update table1 set value = ( select b.value from (select * from table1) b where b.id = table1.id and b.date < table1.date order by b.date desc limit 1 ) where value = 0;

And here is a test: http://rextester.com/YQV55035

UPDATE: You obviously tagged the wrong DBMS. Here is the same query for SQL Server:

update table1 set value = ( select top(1) b.value from table1 b where b.id = table1.id and b.date < table1.date order by b.date desc ) where value = 0;

this does not work, it gives: Incorrect syntax near 'limit'.
This is because you tagged your question with MySQL, but it seems now that your database system is actually SQL Server, which is another product and their SQL dialects differ. I have updated my answer.

Collectives™ on Stack Overflow

Update table in sql query

2 Answers 2

5 Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Related